Country or region and language

    Choose your country or region and your language.

    Suggested for you

      All countries and regions

        Choose your character

        It shows in the top bar and on your profile. Change it any time.

        PostgreSQL cheat sheet for SQL and query plans

        Use these PostgreSQL statements as a syntax reference, then test the result against your data. The examples describe a fictional dispatch team.

        Checked 14 November 2026

        Tables, queries and indexes

        Run the example in a scratch database. The identity column supplies a dispatch ID. The status constraint limits allowed text, while NOT NULL independently rejects a missing status. timestamptz represents an instant; its displayed time depends on the session time zone.

        CREATE TABLE dispatches (
          dispatch_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
          branch text NOT NULL,
          status text NOT NULL CHECK (status IN ('open', 'closed')),
          scheduled_at timestamptz,
          details jsonb NOT NULL DEFAULT '{}'::jsonb
        );
        
        SELECT branch, COUNT(*) AS open_jobs
        FROM dispatches
        WHERE status = 'open'
        GROUP BY branch
        HAVING COUNT(*) > 1
        ORDER BY open_jobs DESC, branch;
        
        CREATE INDEX dispatch_open_branch
          ON dispatches (branch) WHERE status = 'open';
        CREATE INDEX dispatch_priority
          ON dispatches ((details ->> 'priority'));
        CREATE INDEX dispatch_details_gin
          ON dispatches USING gin (details);
        
        SELECT dispatch_id FROM dispatches
        WHERE details @> '{"priority":"high"}'::jsonb;
        
        BEGIN;
        SAVEPOINT before_change;
        UPDATE dispatches SET status = 'closed' WHERE dispatch_id = 1;
        ROLLBACK TO SAVEPOINT before_change;
        COMMIT;
        
        EXPLAIN (ANALYZE, FORMAT JSON)
        SELECT dispatch_id FROM dispatches
        WHERE branch = 'West' AND status = 'open';

        The grouped query filters rows before aggregation and groups before applying HAVING. The second sort key makes tied counts deterministic. COUNT(*) counts rows, while COUNT(column) counts non-null values. Use IS NULL to test for a missing value; = NULL does not produce true.

        Index choices

        The partial B-tree contains open dispatches and supports the matching branch filter. The expression index targets extracted priority text. The GIN index serves supported JSONB operations such as the containment predicate shown. Creating all three is a syntax demonstration, not a recommendation to add every index to an application.

        Each one costs storage and write work.

        An index’s existence does not guarantee its use. Inspect the plan and data distribution in the B-tree lesson and partial and expression indexes. A multicolumn index must be considered against its operator classes, column order and actual query predicates.

        Transactions and plans

        ROLLBACK TO SAVEPOINT undoes the update while keeping the transaction open. COMMIT ends the transaction. In a failed transaction, recover with a rollback before issuing unrelated commands. Changes to sequence counters are an exception to ordinary rollback expectations.

        EXPLAIN estimates a plan. Adding ANALYZE runs the statement, including writes when you use it on a data-changing statement. In a scratch database, wrap an exploratory write in BEGIN and ROLLBACK when appropriate.

        JSONB and security

        -> returns a JSON value; ->> returns text. @> tests containment. A missing key and a JSON null are different cases, so choose the operator before choosing a default value. Practice those cases in JSONB operators.

        Permissions and row policies are separate checks. A role still needs the table privilege; a row policy restricts eligible rows. Table owners normally bypass row policies, and superusers and BYPASSRLS roles bypass them. Test with the intended application role in row-level security.

        For a runnable task with expected results, open PostgreSQL exercises. The course adds checked practice; it is not a terminal with every psql command.

        Sources

        Your Work Gym account

        Save your progress and pick up on another device. Your browser's progress comes with you.

        or

        You can keep practicing without an account.

        Help and feedback

        Ask a question, tell us what is not working, or share an idea. We reply by email.

        What is it about?

        We use it only to reply to you.