Menu
CoddyTech

PostgreSQL Constraints and Schema Design Interview Questions

Foreign keys, CHECK and unique constraints, exclusion constraints, sequences, views, partitioning, generated columns and domains in PostgreSQL.

11 questions
  1. What does ON DELETE CASCADE do on a foreign key in PostgreSQL?

    It deletes the child rows when their parent row is deleted. The alternatives: NO ACTION (the default) and RESTRICT reject the delete, while SET NULL and SET DEFAULT change the child's column.

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

  2. How does a CHECK constraint treat NULL in PostgreSQL?

    A CHECK passes when its expression is true or NULL; only false rejects the row. So CHECK (price > 0) lets a NULL price through: add NOT NULL when the value is required.

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

  3. Can a UNIQUE column hold more than one NULL in PostgreSQL?

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

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

    PostgreSQL
    CREATE 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, add room_id WITH =, which needs the btree_gist extension.

  5. Why do SERIAL and identity columns have gaps in PostgreSQL?

    PostgreSQL
    CREATE 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 | sql and 3 | 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 a ROLLBACK, 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.

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

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

  7. What does REFRESH MATERIALIZED VIEW CONCURRENTLY need, 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 plain REFRESH locks them out until it finishes.

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

    CONCURRENTLY compares 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.

  8. How does table partitioning work in PostgreSQL?

    You declare a parent table with PARTITION BY RANGE, LIST or HASH on 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).

    PostgreSQL
    CREATE 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 DEFAULT partition; without one, that insert fails. Range suits time series: removing an old month is a quick DROP TABLE of its partition, not a huge DELETE. See partitioning.

  9. 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 UNIQUE constraints. The usual follow-up is the cost: a lookup by id alone cannot be pruned, so it probes every partition's index.

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

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

  11. 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 CHECK into each.

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

Coddy programming languages illustration

Learn to code with Coddy

Get started