What does
ON DELETE CASCADEdo on a foreign key in PostgreSQL?It deletes the child rows when their parent row is deleted. The alternatives:
NO ACTION(the default) andRESTRICTreject the delete, whileSET NULLandSET DEFAULTchange the child's column.PostgreSQLCREATE TABLE customers (id int PRIMARY KEY, name text); CREATE TABLE orders ( id int PRIMARY KEY, customer_id int REFERENCES customers (id) ON DELETE CASCADE, total numeric ); INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ben'); INSERT INTO orders VALUES (10, 1, 250), (11, 1, 90), (12, 2, 40); DELETE FROM customers WHERE id = 1; SELECT id, customer_id, total FROM orders;Only order 12 is left. The trap: PostgreSQL does not index the referencing column, so create an index on
orders (customer_id)yourself, or every parent delete scans the child table. See constraints.How does a
CHECKconstraint treat NULL in PostgreSQL?A
CHECKpasses when its expression is true or NULL; only false rejects the row. SoCHECK (price > 0)lets a NULL price through: addNOT NULLwhen the value is required.PostgreSQLCREATE TABLE products ( name text NOT NULL, price numeric CHECK (price > 0), sale_price numeric, CHECK (sale_price < price) ); INSERT INTO products VALUES ('Mouse', 25, 20), ('Cable', NULL, NULL); SELECT name, price, sale_price FROM products ORDER BY name;Both rows go in. A check can compare columns of the same row (
sale_price < price) but cannot look at other rows or tables; that takes a foreign key, a unique constraint or a trigger.Can a
UNIQUEcolumn hold more than one NULL in PostgreSQL?PostgreSQLCREATE TABLE users (id int PRIMARY KEY, phone text UNIQUE); INSERT INTO users VALUES (1, NULL); INSERT INTO users VALUES (2, NULL); INSERT INTO users VALUES (3, '98450 12345'); SELECT count(*) FROM users;It returns 3. A unique constraint treats NULLs as distinct from each other, so any number of rows may have a NULL phone. Since PostgreSQL 15,
UNIQUE NULLS NOT DISTINCT (phone)treats them as equal, and the second NULL insert fails with a duplicate key error. SQL Server differs: its unique constraints allow a single NULL by default.What is an exclusion constraint in PostgreSQL?
It generalizes
UNIQUE: no two rows may match on all the listed operators, for example no two bookings whose time ranges overlap (&&). It needs an index method that supports the operators, usually GiST.PostgreSQLCREATE TABLE booking ( guest text, during tsrange, EXCLUDE USING gist (during WITH &&) ); INSERT INTO booking VALUES ('Asha', '[2026-03-01 10:00, 2026-03-01 11:00)'); INSERT INTO booking VALUES ('Ben', '[2026-03-01 10:30, 2026-03-01 12:00)') ON CONFLICT DO NOTHING; INSERT INTO booking VALUES ('Chen', '[2026-03-01 11:00, 2026-03-01 12:00)'); SELECT guest, during::text AS during FROM booking ORDER BY lower(during);Ben's slot overlaps Asha's, so it is skipped. Chen starts when Asha ends, and half-open
[)ranges do not overlap. For one booking per room, addroom_id WITH =, which needs thebtree_gistextension.Why do
SERIALand identity columns have gaps in PostgreSQL?PostgreSQLCREATE TABLE tags ( id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text UNIQUE ); INSERT INTO tags (name) VALUES ('sql'); INSERT INTO tags (name) VALUES ('sql') ON CONFLICT (name) DO NOTHING; INSERT INTO tags (name) VALUES ('python'); SELECT id, name FROM tags ORDER BY id;It returns
1 | sqland3 | python. The skipped insert took value 2 from the sequence before the conflict was found. A sequence never gives a value back, not after a failed insert and not after aROLLBACK, so concurrent sessions never wait on each other for a number. Treat ids as unique, not consecutive; gapless invoice numbers need a counter row updated in the same transaction.What is the difference between a view and a materialized view in PostgreSQL?
A view is a saved query that runs each time you read it, so it is always current. A materialized view stores the query's rows: fast to read, but stale until you run
REFRESH MATERIALIZED VIEW.PostgreSQLCREATE TABLE orders (id int, amount numeric); INSERT INTO orders VALUES (1, 100), (2, 50); CREATE VIEW order_totals AS SELECT count(*) AS orders, sum(amount) AS revenue FROM orders; CREATE MATERIALIZED VIEW order_totals_mv AS SELECT count(*) AS orders, sum(amount) AS revenue FROM orders; INSERT INTO orders VALUES (3, 25); SELECT 'view' AS source, * FROM order_totals UNION ALL SELECT 'materialized', * FROM order_totals_mv;The view counts 3 orders, the materialized view still 2. Only a materialized view can have its own indexes, and PostgreSQL never refreshes one automatically.
What does
REFRESH MATERIALIZED VIEW CONCURRENTLYneed, and when should you use it?It needs a unique index on the materialized view that uses plain column names and covers all rows (no expression, no
WHERE). In return, queries keep reading the old rows during the refresh, while a plainREFRESHlocks them out until it finishes.PostgreSQLCREATE TABLE sales (region text, amount numeric); INSERT INTO sales VALUES ('north', 100), ('south', 40); CREATE MATERIALIZED VIEW region_sales AS SELECT region, sum(amount) AS total FROM sales GROUP BY region; CREATE UNIQUE INDEX ON region_sales (region); INSERT INTO sales VALUES ('south', 60); REFRESH MATERIALIZED VIEW CONCURRENTLY region_sales; SELECT region, total FROM region_sales ORDER BY region;CONCURRENTLYcompares the new result with the old one and applies only the differences, so it is slower when many rows change. It works only on a view that is already populated.How does table partitioning work in PostgreSQL?
You declare a parent table with
PARTITION BY RANGE,LISTorHASHon a key, then create partitions that each hold one slice. Inserts are routed to the right partition, and queries that filter on the key skip partitions that cannot match (partition pruning).PostgreSQLCREATE TABLE events (id bigint, happened_on date NOT NULL, kind text) PARTITION BY RANGE (happened_on); CREATE TABLE events_2026_01 PARTITION OF events FOR VALUES FROM ('2026-01-01') TO ('2026-02-01'); CREATE TABLE events_2026_02 PARTITION OF events FOR VALUES FROM ('2026-02-01') TO ('2026-03-01'); CREATE TABLE events_other PARTITION OF events DEFAULT; INSERT INTO events VALUES (1, '2026-01-15', 'login'), (2, '2026-02-03', 'buy'), (3, '2025-12-31', 'login'); SELECT tableoid::regclass AS partition, id, happened_on FROM events ORDER BY id;The 2025 row went to the
DEFAULTpartition; without one, that insert fails. Range suits time series: removing an old month is a quickDROP TABLEof its partition, not a hugeDELETE. See partitioning.Why must a primary key on a partitioned table include the partition key?
Because PostgreSQL has no global index: each partition has its own index, so uniqueness can only be checked within one partition. Including the partition key guarantees that equal keys land in the same partition, where its index catches them.
CREATE TABLE events (id bigint PRIMARY KEY, happened_on date NOT NULL) PARTITION BY RANGE (happened_on); -- ERROR: unique constraint on partitioned table must include all partitioning columns CREATE TABLE events (id bigint, happened_on date NOT NULL, PRIMARY KEY (id, happened_on)) PARTITION BY RANGE (happened_on);The same rule applies to
UNIQUEconstraints. The usual follow-up is the cost: a lookup byidalone cannot be pruned, so it probes every partition's index.What is a generated column in PostgreSQL?
A column computed from other columns of the same row, declared
GENERATED ALWAYS AS (expression) STORED. It is recomputed on every insert and update, and you cannot write to it.PostgreSQLCREATE TABLE line_items ( qty int NOT NULL, unit_price numeric(10,2) NOT NULL, total numeric(12,2) GENERATED ALWAYS AS (qty * unit_price) STORED ); INSERT INTO line_items (qty, unit_price) VALUES (3, 19.99), (10, 2.50); UPDATE line_items SET qty = 4 WHERE unit_price = 19.99; SELECT qty, unit_price, total FROM line_items ORDER BY qty;After the update, the first total is 79.96. PostgreSQL 17 has only stored generated columns; virtual ones arrived in PostgreSQL 18. The expression must be immutable, so no
now()and no other tables; use a trigger for those.What is a domain in PostgreSQL, and when would you use one?
A domain is a named type built on a base type plus constraints. Define a rule once and reuse it in every table, instead of copying the same
CHECKinto each.PostgreSQLCREATE DOMAIN email AS text CHECK (VALUE ~ '^[^@\s]+@[^@\s]+$'); CREATE DOMAIN positive_amount AS numeric(10,2) CHECK (VALUE > 0); CREATE TABLE invoices ( id int PRIMARY KEY, billed_to email, amount positive_amount ); INSERT INTO invoices VALUES (1, 'asha@example.com', 120.00); SELECT id, billed_to, pg_typeof(billed_to) AS type FROM invoices;Inserting
'not-an-email'fails with "value for domain email violates check constraint", and so does the cast'not-an-email'::email, so the rule also guards function arguments.
PostgreSQL Constraints and Schema Design Interview Questions
Foreign keys, CHECK and unique constraints, exclusion constraints, sequences, views, partitioning, generated columns and domains in PostgreSQL.