What types of joins does PostgreSQL support?
INNER JOIN(matching rows only),LEFTandRIGHT JOIN(every row of one side, NULLs where nothing matches),FULL JOIN(every row of both sides) andCROSS JOIN(every pair). Unlike MySQL, PostgreSQL supportsFULL JOIN.PostgreSQLCREATE TABLE customers (id int PRIMARY KEY, name text); CREATE TABLE orders (id int PRIMARY KEY, customer_id int, amount numeric); INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ben'), (3, 'Chen'); INSERT INTO orders VALUES (10, 1, 120), (11, 1, 30), (12, 4, 75); SELECT c.name, o.id AS order_id FROM customers c FULL JOIN orders o ON o.customer_id = c.id ORDER BY c.id, o.id;Asha appears twice, Ben and Chen get a NULL order, and order 12, whose customer 4 does not exist, gets a NULL name. A self join is one table joined to itself under two aliases. More join questions in the SQL interview questions.
What does
DISTINCT ONdo in PostgreSQL?DISTINCT ON (cols)keeps the first row of each group with equalcols, whereORDER BYdecides which is first. It is a PostgreSQL extension and the shortest way to get the latest row per customer.PostgreSQLCREATE TABLE orders (id int, customer text, amount numeric, ordered_at date); INSERT INTO orders VALUES (1, 'Asha', 120, '2026-11-01'), (2, 'Asha', 30, '2026-11-09'), (3, 'Ben', 75, '2026-11-03'), (4, 'Ben', 60, '2026-11-02'); SELECT DISTINCT ON (customer) customer, id, amount, ordered_at::text FROM orders ORDER BY customer, ordered_at DESC;It returns Asha's order 2 (November 9) and Ben's order 3 (November 3).
ORDER BYmust start with theDISTINCT ONexpressions, or PostgreSQL raises an error; the columns after them pick the winner.How do you do an upsert in PostgreSQL with
ON CONFLICT?INSERT ... ON CONFLICT (key) DO UPDATEinserts a row, or updates the existing one when a unique constraint would break;DO NOTHINGskips it.EXCLUDEDis the row you tried to insert.PostgreSQLCREATE TABLE stock (sku text PRIMARY KEY, qty int); INSERT INTO stock VALUES ('pen', 10); INSERT INTO stock VALUES ('pen', 5), ('ink', 3) ON CONFLICT (sku) DO UPDATE SET qty = stock.qty + EXCLUDED.qty; SELECT sku, qty FROM stock ORDER BY sku;It returns
ink | 3andpen | 15. UnlikeSELECTthenINSERT, it is atomic, so two sessions writing one key do not race. The target needs a unique index on those columns, and one statement cannot hit a row twice: two'pen'rows in oneVALUESlist fail with "cannot affect row a second time". See INSERT.How do you get the id of an inserted row in PostgreSQL?
Add
RETURNINGto theINSERT: it returns the columns you list for each inserted row, including values filled by defaults and identity columns, in the same round trip.PostgreSQLCREATE TABLE users ( id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email text ); INSERT INTO users (email) VALUES ('asha@example.com'), ('ben@example.com') RETURNING id, email;It returns
1 | asha@example.comand2 | ben@example.com.UPDATE,DELETEand, since PostgreSQL 17,MERGEtakeRETURNINGtoo, so you get the changed rows without a second query. AvoidSELECT max(id)afterwards: another session may have inserted in between.What is a CTE in PostgreSQL, and is it materialized?
A CTE (
WITH name AS (...)) is a named subquery for one statement, used to make a long query read top to bottom. Since PostgreSQL 12, a CTE that is not recursive, has no side effects and is referenced once is inlined, so the planner optimizes through it. Before 12, every CTE was computed once and stored, which blocked optimization.PostgreSQLCREATE TABLE orders (id int, customer text, amount numeric); INSERT INTO orders VALUES (1, 'Asha', 120), (2, 'Asha', 30), (3, 'Ben', 75), (4, 'Chen', 20); WITH totals AS ( SELECT customer, sum(amount) AS total FROM orders GROUP BY customer ) SELECT customer, total FROM totals WHERE total > (SELECT avg(total) FROM totals);It returns
Asha | 150.totalsis referenced twice, so it is computed once. Force either choice withAS MATERIALIZEDorAS NOT MATERIALIZED.How does a recursive CTE work in PostgreSQL, and when does it stop?
PostgreSQLWITH RECURSIVE n(x) AS ( SELECT 1 UNION ALL SELECT x * 2 FROM n WHERE x < 20 ) SELECT max(x) FROM n;It returns
32. A recursive CTE has an anchor (SELECT 1) and a recursive part that reads the rows of the previous step; it stops when a step returns no rows.WHERE x < 20tests the old value: 16 passes and produces 32, then 32 fails and the loop ends.The usual job is walking a tree, such as an org chart. For graphs that may loop, PostgreSQL 14 added the
CYCLEclause. For a plain sequence,generate_series(1, 10)is simpler. See WITH queries.What is a
LATERALjoin in PostgreSQL?LATERALlets a subquery inFROMuse columns of the tables listed before it. It runs once per outer row, like a correlated subquery that can return several rows and columns.PostgreSQLCREATE TABLE customers (id int, name text); CREATE TABLE orders (id int, customer_id int, amount numeric, ordered_at date); INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ben'), (3, 'Chen'); INSERT INTO orders VALUES (1, 1, 120, '2026-11-01'), (2, 1, 30, '2026-11-09'), (3, 1, 50, '2026-11-05'), (4, 2, 75, '2026-11-03'); SELECT c.name, o.amount, o.ordered_at::text FROM customers c LEFT JOIN LATERAL ( SELECT amount, ordered_at FROM orders WHERE customer_id = c.id ORDER BY ordered_at DESC LIMIT 2 ) o ON true ORDER BY c.name, o.ordered_at DESC;It returns Asha's two latest orders, Ben's one order, and a NULL row for Chen, kept by
LEFT JOIN ... ON true. With an index on(customer_id, ordered_at)this is a fast top N per group. A set-returning function inFROM, such asjsonb_array_elements(t.data), is lateral without the keyword.What is the difference between
ROW_NUMBER,RANKandDENSE_RANKin PostgreSQL?PostgreSQLCREATE TABLE scores (name text, score int); INSERT INTO scores VALUES ('Asha', 90), ('Ben', 90), ('Chen', 80); SELECT score, ROW_NUMBER() OVER (ORDER BY score DESC) AS rn, RANK() OVER (ORDER BY score DESC) AS rnk, DENSE_RANK() OVER (ORDER BY score DESC) AS dense FROM scores ORDER BY rn;It returns 1, 2, 3 for
ROW_NUMBER, 1, 1, 3 forRANKand 1, 1, 2 forDENSE_RANK.ROW_NUMBERnumbers every row, breaking ties arbitrarily;RANKgives ties one number, then skips;DENSE_RANKleaves no gap.PARTITION BY deptrestarts the numbering per group. You cannot filter on a window function inWHERE: compute it in a subquery or CTE and filter outside. More in window functions.What does the
FILTERclause do on an aggregate in PostgreSQL?PostgreSQLCREATE TABLE orders (amount int); INSERT INTO orders VALUES (20), (60), (80), (40); SELECT count(*), count(*) FILTER (WHERE amount > 50), sum(amount) FILTER (WHERE amount > 100) FROM orders;It returns
4 | 2 | NULL.FILTER (WHERE ...)feeds an aggregate only the rows that pass, so one query can count several conditions side by side. It is the SQL standard form ofsum(CASE WHEN ... THEN 1 ELSE 0 END), supported since PostgreSQL 9.4.The trap is the last column: no amount is over 100, and
sumover no rows is NULL, not 0. WriteCOALESCE(sum(amount) FILTER (WHERE amount > 100), 0)when you need a number.countis the exception and returns 0.How do you combine values from several rows into one string or array in PostgreSQL?
Use
string_agg(col, separator)for a string andarray_agg(col)for an array. Both are aggregates and accept anORDER BYinside the call;string_aggis PostgreSQL's answer to MySQL'sGROUP_CONCAT.PostgreSQLCREATE TABLE skills (person text, skill text); INSERT INTO skills VALUES ('Asha', 'sql'), ('Asha', 'go'), ('Ben', 'python'), ('Asha', 'aws'); SELECT person, string_agg(skill, ', ' ORDER BY skill) AS skills, array_agg(skill ORDER BY skill) AS skill_array FROM skills GROUP BY person ORDER BY person;Asha gets
aws, go, sql. Without the innerORDER BY, the order is not defined.string_aggskips NULL values butarray_aggkeeps them.json_aggbuilds a JSON array the same way, andunnest(array)turns an array back into rows.What is the difference between
UNIONandUNION ALL, and how doINTERSECTandEXCEPTwork?PostgreSQLSELECT 1 UNION SELECT 1 UNION ALL SELECT 1;It returns two rows of
1. Set operators of the same kind run left to right:SELECT 1 UNION SELECT 1removes the duplicate and leaves one row, thenUNION ALLappends the third without checking.Removing duplicates costs a sort or hash, so prefer
UNION ALLwhen duplicates cannot happen.INTERSECTkeeps rows in both inputs,EXCEPTkeeps rows of the first missing from the second (Oracle'sMINUS), and both remove duplicates unless you addALL.INTERSECTbinds tighter thanUNION.
PostgreSQL Query Interview Questions
Joins, DISTINCT ON, upserts, RETURNING, CTEs, LATERAL, window functions, FILTER, string and array aggregates, and set operators.