Interview Q&A / PostgreSQL
PostgreSQL interview questions
Indexes, EXPLAIN, transactions and isolation, JSONB and the Postgres specifics backend interviews probe.
Prefer rapid revision? Practise PostgreSQL short interview answers →
- How do you use a CHECK constraint, and what does it ignore?
CHECK rejects a row when a boolean expression on its columns is false. It passes when the result is true or NULL, so pair it with NOT NULL when the column must always be present. It cannot see other rows.
BeginnerQuick answer1 min read - What do ON DELETE CASCADE, RESTRICT, SET NULL and NO ACTION mean on a foreign key?
They decide what happens to child rows when the parent row is deleted. CASCADE deletes the children, SET NULL clears the reference, RESTRICT blocks the delete immediately, and NO ACTION blocks it at the end of the statement (the default).
BeginnerQuick answer1 min read - Can a UNIQUE column in PostgreSQL contain more than one NULL?
Yes by default, because NULL is unknown and two unknowns are not considered equal. From PostgreSQL 15 you can add NULLS NOT DISTINCT to make NULLs count as duplicates.
IntermediateQuick answer1 min read - Should you use an ENUM type, a CHECK constraint, or a lookup table for a fixed set of values?
A CHECK on text is the easiest to change, an ENUM is compact and ordered but awkward to alter, and a lookup table with a foreign key is best when the values need extra data or change often.
IntermediateQuick answer1 min read - How do you add a foreign key or CHECK constraint to a huge table without a long lock?
Add it as NOT VALID, which skips the check of existing rows and only needs a brief lock, then run VALIDATE CONSTRAINT separately. Validation scans the table but only takes a weaker lock, so writes continue.
AdvancedQuick answer1 min read - How do you stop two bookings from overlapping in PostgreSQL?
Use an exclusion constraint with a range column and the overlap operator && on a GiST index. The database then rejects any row whose range overlaps an existing row for the same resource.
AdvancedQuick answer1 min read - What is a DEFERRABLE constraint in PostgreSQL and when is it needed?
A deferrable constraint can be checked at COMMIT instead of after each statement. This lets you insert rows that reference each other, or swap unique values, as long as everything is consistent by the end of the transaction.
AdvancedQuick answer1 min read