SQL Mastery for PostgreSQL DBAs
SQL mastery for a DBA is fluent use of PostgreSQL's SQL dialect and type system: window functions, CTEs, JSONB, arrays and the constraints that keep data correct. A DBA reads and rewrites application queries, so strong SQL and sound constraint design form the base of the PostgreSQL DBA path.
Stage: Foundations · Part of the PostgreSQL DBA roadmap · Last reviewed 19 November 2026
What to learn in SQL Mastery
- Write recursive CTEs and window functions in PostgreSQL
- Query JSONB columns with the arrow and containment operators and index them
- Pick between numeric, bigint, text and timestamptz for a column and justify it
- Enforce rules with PRIMARY KEY, UNIQUE, CHECK, FOREIGN KEY and exclusion constraints
- Use INSERT with ON CONFLICT for safe upserts
SQL Mastery interview questions
Why is timestamptz usually preferred over timestamp in PostgreSQL?
timestamptz stores an absolute instant, converting input to UTC and displaying it in the session time zone, so comparisons stay unambiguous across regions and daylight saving. Plain timestamp stores a wall-clock value with no zone, so the same value can mean different moments.
What does INSERT ... ON CONFLICT DO UPDATE do, and what does it require?
It inserts a row, and if that violates the unique constraint or index named in the conflict target, it updates the existing row instead, with EXCLUDED referring to the proposed values. It needs a unique index or constraint to arbitrate and is atomic under concurrency.
Where SQL Mastery fits in the PostgreSQL DBA roadmap
SQL Mastery belongs to the Foundations stage (beginner level), which takes about 6 hours. Relational basics and the Postgres way.
Other units in this stage
Back to the full PostgreSQL DBA roadmap