skip to content

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 pageshow

explore

questions

page 2 of 2

On 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?

level: seniorimportance: should knowfreq 37%

basics

~20 s

Add 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.

open as a page

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?

level: seniorimportance: should knowfreq 30%

basics

~20 s

DO 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.

open as a page

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?

level: seniorimportance: should knowfreq 45%

basics

~20 s

MERGE 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.

open as a page

In PostgreSQL 18, why is CREATE UNLOGGED TABLE clicks (...) PARTITION BY RANGE (clicked_at) rejected, and where do UNLOGGED and storage settings live instead?

level: middleimportance: nice to knowfreq 22%

basics

~10 s

Since 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.

open as a page

In PostgreSQL, how do you use generate_series to return a row for every minute of a trading session, including minutes without ticks?

level: middleimportance: nice to knowfreq 30%

basics

~10 s

generate_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.

open as a page

In PostgreSQL, which constraints can be declared DEFERRABLE, and what do you give up by making a UNIQUE or EXCLUDE constraint deferrable?

level: seniorimportance: nice to knowfreq 20%

basics

~20 s

Only 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.

open as a page

In PostgreSQL, what does COPY ... WITH (FREEZE) do, when is it allowed, and which MVCC rule does it break?

level: seniorimportance: nice to knowfreq 25%

basics

~20 s

COPY 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.

open as a page

showing 31–37 of 37