Menu
CoddyTech

PostgreSQL Query Interview Questions

Joins, DISTINCT ON, upserts, RETURNING, CTEs, LATERAL, window functions, FILTER, string and array aggregates, and set operators.

11 questions
  1. What types of joins does PostgreSQL support?

    INNER JOIN (matching rows only), LEFT and RIGHT JOIN (every row of one side, NULLs where nothing matches), FULL JOIN (every row of both sides) and CROSS JOIN (every pair). Unlike MySQL, PostgreSQL supports FULL JOIN.

    PostgreSQL
    CREATE 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.

  2. What does DISTINCT ON do in PostgreSQL?

    DISTINCT ON (cols) keeps the first row of each group with equal cols, where ORDER BY decides which is first. It is a PostgreSQL extension and the shortest way to get the latest row per customer.

    PostgreSQL
    CREATE 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 BY must start with the DISTINCT ON expressions, or PostgreSQL raises an error; the columns after them pick the winner.

  3. How do you do an upsert in PostgreSQL with ON CONFLICT?

    INSERT ... ON CONFLICT (key) DO UPDATE inserts a row, or updates the existing one when a unique constraint would break; DO NOTHING skips it. EXCLUDED is the row you tried to insert.

    PostgreSQL
    CREATE 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 | 3 and pen | 15. Unlike SELECT then INSERT, 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 one VALUES list fail with "cannot affect row a second time". See INSERT.

  4. How do you get the id of an inserted row in PostgreSQL?

    Add RETURNING to the INSERT: it returns the columns you list for each inserted row, including values filled by defaults and identity columns, in the same round trip.

    PostgreSQL
    CREATE 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.com and 2 | ben@example.com. UPDATE, DELETE and, since PostgreSQL 17, MERGE take RETURNING too, so you get the changed rows without a second query. Avoid SELECT max(id) afterwards: another session may have inserted in between.

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

    PostgreSQL
    CREATE 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. totals is referenced twice, so it is computed once. Force either choice with AS MATERIALIZED or AS NOT MATERIALIZED.

  6. How does a recursive CTE work in PostgreSQL, and when does it stop?

    PostgreSQL
    WITH 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 < 20 tests 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 CYCLE clause. For a plain sequence, generate_series(1, 10) is simpler. See WITH queries.

  7. What is a LATERAL join in PostgreSQL?

    LATERAL lets a subquery in FROM use columns of the tables listed before it. It runs once per outer row, like a correlated subquery that can return several rows and columns.

    PostgreSQL
    CREATE 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 in FROM, such as jsonb_array_elements(t.data), is lateral without the keyword.

  8. What is the difference between ROW_NUMBER, RANK and DENSE_RANK in PostgreSQL?

    PostgreSQL
    CREATE 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 for RANK and 1, 1, 2 for DENSE_RANK. ROW_NUMBER numbers every row, breaking ties arbitrarily; RANK gives ties one number, then skips; DENSE_RANK leaves no gap.

    PARTITION BY dept restarts the numbering per group. You cannot filter on a window function in WHERE: compute it in a subquery or CTE and filter outside. More in window functions.

  9. What does the FILTER clause do on an aggregate in PostgreSQL?

    PostgreSQL
    CREATE 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 of sum(CASE WHEN ... THEN 1 ELSE 0 END), supported since PostgreSQL 9.4.

    The trap is the last column: no amount is over 100, and sum over no rows is NULL, not 0. Write COALESCE(sum(amount) FILTER (WHERE amount > 100), 0) when you need a number. count is the exception and returns 0.

  10. How do you combine values from several rows into one string or array in PostgreSQL?

    Use string_agg(col, separator) for a string and array_agg(col) for an array. Both are aggregates and accept an ORDER BY inside the call; string_agg is PostgreSQL's answer to MySQL's GROUP_CONCAT.

    PostgreSQL
    CREATE 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 inner ORDER BY, the order is not defined. string_agg skips NULL values but array_agg keeps them. json_agg builds a JSON array the same way, and unnest(array) turns an array back into rows.

  11. What is the difference between UNION and UNION ALL, and how do INTERSECT and EXCEPT work?

    PostgreSQL
    SELECT 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 1 removes the duplicate and leaves one row, then UNION ALL appends the third without checking.

    Removing duplicates costs a sort or hash, so prefer UNION ALL when duplicates cannot happen. INTERSECT keeps rows in both inputs, EXCEPT keeps rows of the first missing from the second (Oracle's MINUS), and both remove duplicates unless you add ALL. INTERSECT binds tighter than UNION.

Coddy programming languages illustration

Learn to code with Coddy

Get started