Available learning path

PostgreSQL · DML · Administration

PostgreSQL Pro

Structure, modify, automate, secure and optimise a PostgreSQL database in an isolated laboratory.

Start the pathSyllabus

Prerequisites

Master SQL Foundations and read a multi-step analytical query.

Syllabus

8 structured lessons

  1. 01

    Impose integrity with constraints

    Choose between type, NOT NULL, UNIQUE, CHECK, PRIMARY KEY and FOREIGN KEY.

    34 min · Related practice: Guarantee integrity with real constraints
    Access required →
  2. 02

    Modify data with a secure DML

    Master INSERT, UPDATE, DELETE and UPSERT with a controlled perimeter.

    36 min · Related practice: Edit data without losing control
    Access required →
  3. 03

    Protect an operation with transactions

    Use COMMIT, ROLLBACK and SAVEPOINT by understanding the competition.

    40 min · Related practice: Secure an operation with transactions
    Access required →
  4. 04

    Reuse logic with views and materialized views

    Choose between always fresh result and pre-calculated result.

    34 min · Related practice: Create and maintain reliable views
    Access required →
  5. 05

    Accelerate with indexes and read EXPLAIN

    Link an index to a real query and measure its cost.

    42 min · Related practice: Choose an index and read EXPLAIN
    Access required →
  6. 06

    Encapsulate logic with functions and procedures

    Choose a reusable object without hiding logic unnecessarily.

    42 min · Related practice: Write testable PostgreSQL functions
    Access required →
  7. 07

    Automate an audit with triggers

    Use OLD, NEW, BEFORE, and AFTER without creating invisible logic.

    45 min · Related practice: Automate an audit with triggers
    Access required →
  8. 08

    Control access with roles and permissions

    Enforce least privilege with GRANT, REVOKE, and group roles.

    38 min · Related practice: Test roles and permissions positively and negatively
    Access required →