SQL Dialect
PostgreSQL extends standard SQL with DISTINCT ON, ON CONFLICT upserts, RETURNING, COPY, identity and uuidv7 keys, exclusion constraints and partitioning. Interviewers probe the production edges.
part ofPostgreSQLread it →on this pageshowhide
explore
- Upserts, MERGE & RETURNING5 questions
- Bulk Loading with COPY5 questions
- UUIDs, Serial & Generated Columns5 questions
- Exclusion & Temporal Constraints5 questions
- Online Schema Changes5 questions
- Declarative Partitioning Rules6 questions
- DISTINCT ON, CTEs & Regex6 questions
questions
page 2 of 2On a busy PostgreSQL 18 accounts table, how do you add a NOT NULL signup_country column that needs a backfill without a long ACCESS EXCLUSIVE scan?
basics
~20 sAdd the column nullable and backfill in batches; then, in PostgreSQL 18, add the not-null constraint NOT VALID and VALIDATE it under SHARE UPDATE EXCLUSIVE. On 17, a validated CHECK (signup_country IS NOT NULL) lets SET NOT NULL skip its scan.
A PostgreSQL get-or-create query puts INSERT ... ON CONFLICT DO NOTHING RETURNING brand_id in a WITH clause, UNION ALL a SELECT of the existing row, yet sometimes returns nothing; why?
basics
~20 sDO NOTHING returns no row for a conflict, and the fallback SELECT shares the statement's snapshot, so a brand committed by a concurrent transaction after that snapshot is invisible to it; the query returns zero rows although the brand exists.
Two overlapping runs of a PostgreSQL product-feed sync both use MERGE ... WHEN NOT MATCHED THEN INSERT, and one fails with SQLSTATE 23505; why, and would INSERT ... ON CONFLICT avoid it?
basics
~20 sMERGE picks MATCHED or NOT MATCHED by joining against its snapshot, so a row the other run inserts concurrently is invisible, the INSERT arm fires, and the unique index raises 23505. ON CONFLICT DO UPDATE would have updated that row instead.
In PostgreSQL 18, why is CREATE UNLOGGED TABLE clicks (...) PARTITION BY RANGE (clicked_at) rejected, and where do UNLOGGED and storage settings live instead?
basics
~10 sSince PostgreSQL 18 a partitioned table cannot be unlogged: it has no storage, and the flag never reached its partitions anyway. Persistence, like fillfactor and autovacuum settings, is set on each leaf partition.
In PostgreSQL, how do you use generate_series to return a row for every minute of a trading session, including minutes without ticks?
basics
~10 sgenerate_series(start, stop, interval '1 minute') over timestamptz produces one row per minute; LEFT JOIN the ticks aggregated by date_trunc('minute', traded_at) to it, so minutes without trades appear with NULL values.
In PostgreSQL, which constraints can be declared DEFERRABLE, and what do you give up by making a UNIQUE or EXCLUDE constraint deferrable?
basics
~20 sOnly UNIQUE, PRIMARY KEY, EXCLUDE and FOREIGN KEY constraints can be DEFERRABLE. A deferrable UNIQUE or EXCLUDE cannot arbitrate ON CONFLICT, a deferrable unique key cannot be a foreign key target, checking gets slower, and errors surface later.
In PostgreSQL, what does COPY ... WITH (FREEZE) do, when is it allowed, and which MVCC rule does it break?
basics
~20 sCOPY FREEZE writes rows already frozen and marks their pages all-visible and all-frozen, sparing later vacuum work. It needs the table created or truncated in the current subtransaction, and committed rows become visible even to older snapshots.
showing 31–37 of 37