PostgreSQL toolkit

Use PostgreSQL features with explicit grain

Arrays, JSONB, lateral expansion, and DISTINCT ON are useful extensions of ordinary relational patterns. Expansion can multiply rows; aggregation can collect them again. Keep track of those transitions.

Suggested route: Start from the quick reference, then run the arrays and JSON examples with empty and null inputs. Connect views and routines to their lifecycle and access rules.

By the end: Explain when a PostgreSQL-specific construct improves clarity and what must change in another dialect.

Quick reference

DISTINCT ON

Latest row per customer:

SELECT DISTINCT ON (customer_id)
       customer_id, order_id, order_ts
FROM orders
ORDER BY customer_id, order_ts DESC, order_id DESC;

The ORDER BY chooses which row survives. For portable SQL, use ROW_NUMBER().

Arrays: expand and preserve position

SELECT u.user_id, x.skill, x.position
FROM user_skills u
CROSS JOIN LATERAL
     UNNEST(u.skills) WITH ORDINALITY AS x(skill, position);

Use LEFT JOIN LATERAL ... ON TRUE when users with null/empty expansions must remain.

Membership without expansion:

WHERE 'SQL' = ANY(skills)

JSONB: extract vs expand

payload -> 'user'         -- JSON/JSONB value
payload ->> 'user_id'     -- text value

Use expansion functions when one JSON array element should become one SQL row. Expansion changes grain, so check row multiplication just as you would with a join.

Upsert and changed-row output

INSERT INTO users(user_id, name)
VALUES (1, 'Kayvan')
ON CONFLICT (user_id)
DO UPDATE SET name = EXCLUDED.name
RETURNING *;

ON CONFLICT relies on an applicable unique/exclusion constraint. RETURNING is useful when the caller needs generated IDs or the final stored row.

High-value PostgreSQL combinations

GENERATE_SERIES + LEFT JOIN
    missing-date/calendar reports

UNNEST + WITH ORDINALITY
    expand while keeping original array order

LEFT JOIN LATERAL + ORDER BY + LIMIT
    latest/top-N related rows while preserving parents

FILTER + aggregate
    concise conditional metrics

DISTINCT ON + ORDER BY
    PostgreSQL-specific latest/earliest record

JSONB expansion + LATERAL
    nested repeated data → relational rows

Common traps

• Two independent UNNEST/expansion operations can multiply row counts.
• DISTINCT ON without deterministic ORDER BY can choose an unintended survivor.
• -> returns JSON; ->> returns text.
• ON CONFLICT is not a substitute for deciding the correct uniqueness key.
• PostgreSQL-specific shortcuts are useful in interviews only when the dialect permits them.

In this chapter

• Arrays & UNNESTExpand arrays, preserve empty rows, and avoid accidental products.
• DISTINCT ON & string typesRecognize useful PostgreSQL-specific choices.
• JSONB extraction & expansionQuery nested data while keeping types, missing keys, and row counts explicit.
• Views & materialized viewsSeparate reusable query definitions from stored query results.
• Functions, procedures & triggersRecognize when logic belongs in a database routine and when hidden behavior adds risk.
• Privileges & parameterized queriesSeparate SQL values from SQL code and give database roles only the access they need.

How to study

Treat this page as a dialect toolbox. Know which constructs are PostgreSQL-specific and be ready to give the portable alternative where one exists.