Welcome To TechBrothersIT: PostgreSQL Interview Questions and Answers Part 9

PostgreSQL Interview Q&A · Part 9 of 10

In this post we are going to cover upserts and data changes in PostgreSQL: INSERT ON CONFLICT, RETURNING, MERGE, removing duplicates, UPDATE with a join, and loading data in bulk with COPY. These are 7 real PostgreSQL interview questions, asked the way they come up on the job.

Reading data is half of SQL. The other half is changing it safely: loading a feed twice without creating duplicates, getting generated keys back, and moving millions of rows without locking the system for hours. Postgres has its own syntax for much of this, and interviewers expect you to know it.

Every query below is complete, so you can copy it, run it on the sample data and get exactly the output shown under it.

VIDEO COMING SOON

Part 9 of the Real Time PostgreSQL Interview Questions and Answers series on TechBrothersIT.

On this page

Let's create some sample data
Question 1: A nightly feed sends inventory rows. If the SKU is new, the row must be inserted. If the SKU already exists, its quantity must be updated. How do you do this upsert in one statement in PostgreSQL?
Question 2: A message queue sometimes delivers the same event twice. The loader must skip events it has already stored, without an error and without checking first. How do you write the insert?
Question 3: After inserting a signup, the application needs the generated ID and the default values, and today it runs a second SELECT to get them. How do you avoid the second round trip?
Question 4: PostgreSQL 15 added the MERGE statement. A developer wants to replace every ON CONFLICT upsert with MERGE, because it is standard SQL. Is that a good idea?
Question 5: The contacts table has the same person several times, with emails that differ only in upper and lower case. You must delete the duplicates and keep the most recently updated row for each email. How?
Question 6: A staging table holds new prices for some products. You need to update the products table from it. A teammate's UPDATE ran without error, but one product got the wrong price. What is the correct pattern, and what went wrong?
Question 7: A job loads a CSV file with millions of rows by sending one INSERT per row, and it runs for hours. The data must be upserted into an existing table. How do you make it fast?
Key points to remember
The full SQL script

Let's create some sample data

This post uses several small tables: inventory and its daily inventory_feed, processed_events, signups with an identity column, contacts with duplicate people, products with a price_updates staging table, and a stock table with 50,000 rows for the bulk load. The script below creates everything this post needs. It was written for and run on PostgreSQL 16.

Sample data - SQL script (PostgreSQL)

CREATE TABLE inventory (sku TEXT PRIMARY KEY, product TEXT, qty INT, updated_on DATE);
INSERT INTO inventory VALUES
  ('LAPTOP', 'Laptop', 10, '2026-06-01'),
  ('MOUSE',  'Mouse',  50, '2026-06-01'),
  ('DESK',   'Desk',    5, '2026-06-01');

CREATE TABLE inventory_feed (sku TEXT, product TEXT, qty INT);      -- tomorrow's file from the warehouse
INSERT INTO inventory_feed VALUES ('LAPTOP', 'Laptop', 7), ('MOUSE', 'Mouse', 65), ('LAMP', 'Lamp', 30);

CREATE TABLE processed_events (event_id INT PRIMARY KEY, received_at DATE);
INSERT INTO processed_events VALUES (101, '2026-06-01'), (102, '2026-06-01');

CREATE TABLE signups (
  signup_id  INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      TEXT,
  plan       TEXT DEFAULT 'free',
  created_on DATE DEFAULT DATE '2026-06-02'
);

CREATE TABLE contacts (contact_id INT PRIMARY KEY, email TEXT, name TEXT, updated_on DATE);
INSERT INTO contacts VALUES
  (1, 'sara@mail.com', 'Sara Khan',  '2026-01-10'),
  (2, 'omar@mail.com', 'Omar Ali',   '2026-02-01'),
  (3, 'Sara@Mail.com', 'Sara K.',    '2026-05-20'),   -- same person, newer row
  (4, 'lina@mail.com', 'Lina Park',  '2026-03-03'),
  (5, 'omar@mail.com', 'Omar A.',    '2026-01-15'),   -- same person, older row
  (6, 'sara@mail.com', 'S. Khan',    '2026-03-30');

CREATE TABLE products (product_id INT PRIMARY KEY, product TEXT, price NUMERIC(10,2));
INSERT INTO products VALUES (1, 'Laptop', 1200.00), (2, 'Monitor', 300.00), (3, 'Keyboard', 50.00);

CREATE TABLE price_updates (product_id INT, new_price NUMERIC(10,2), loaded_at TIMESTAMP);
INSERT INTO price_updates VALUES
  (1, 1100.00, '2026-06-02 08:00'),
  (3,   55.00, '2026-06-02 08:00'),
  (3,   60.00, '2026-06-02 09:30');     -- product 3 was sent twice

CREATE TABLE stock (sku TEXT PRIMARY KEY, qty INT);
INSERT INTO stock SELECT 'SKU-' || g, 0 FROM generate_series(1, 50000) AS g;   -- 50,000 existing rows
CREATE UNLOGGED TABLE stock_staging (sku TEXT, qty INT);

Question 1: A nightly feed sends inventory rows. If the SKU is new, the row must be inserted. If the SKU already exists, its quantity must be updated. How do you do this upsert in one statement in PostgreSQL?

Say this first

Use INSERT ... ON CONFLICT (sku) DO UPDATE. Postgres tries the insert, and when the unique key already exists it runs the update instead. Inside the update, EXCLUDED is the row you tried to insert.

Two rows arrive. CHAIR is not in the table. No conflict, so it is inserted. LAPTOP is already there. The insert hits the primary key, and instead of failing, Postgres switches to the DO UPDATE branch. In that branch, EXCLUDED dot quantity is the incoming value, 8, and the table row still holds the old value, 10.

incoming rows

sku	qty
LAPTOP	8
CHAIR	12

no conflict

INSERT

key exists

DO UPDATEqty = EXCLUDED.qty

inventory

sku	qty
LAPTOP	10 → 8
MOUSE	50
DESK	5
CHAIR	12

EXCLUDED = the row proposed for insertion. The table name = the existing row.

Here is the statement. The conflict target is the SKU column, which has the primary key. On conflict, we set the quantity and the date from EXCLUDED. Postgres reports INSERT 0 2: two rows were inserted or updated.

SQL - upsert inventory

INSERT INTO inventory (sku, product, qty, updated_on)
VALUES ('LAPTOP', 'Laptop',  8, '2026-06-02'),
       ('CHAIR',  'Chair',  12, '2026-06-02')
ON CONFLICT (sku) DO UPDATE
SET qty        = EXCLUDED.qty,
    updated_on = EXCLUDED.updated_on;

Result: INSERT 0 2

And the table afterwards. LAPTOP went from 10 to 8 with the new date, CHAIR is a new row, and MOUSE and DESK were not touched.

SQL - inventory after

SELECT * FROM inventory ORDER BY sku;

Output (PostgreSQL 16)4 rows

sku	product	qty	updated_on
CHAIR	Chair	12	2026-06-02
DESK	Desk	5	2026-06-01
LAPTOP	Laptop	8	2026-06-02
MOUSE	Mouse	50	2026-06-01

Two details for the interview. The whole thing is atomic, even with many sessions inserting the same key at once. And you can add a WHERE clause to the update, for example to overwrite only when the incoming row is newer.

Interview tip

DO UPDATE SET ... WHERE EXCLUDED.updated_on > inventory.updated_on: only newer data wins.

Question 2: A message queue sometimes delivers the same event twice. The loader must skip events it has already stored, without an error and without checking first. How do you write the insert?

Say this first

Use ON CONFLICT DO NOTHING. Rows whose key already exists are skipped silently, and the command tag tells you how many rows were really inserted. It needs a unique index or constraint to detect the conflict.

Three events arrive, and one of them is a repeat. 101 is already stored. The insert conflicts, and DO NOTHING drops it quietly. 103 and 104 are new, so they go in. The statement succeeds, and you can run the same batch again as many times as you like. That is what idempotent means.

batch

event_id
101
103
104

ON CONFLICT DO NOTHING

processed_events

event_id
101
102
103
104

101: already there → skipped

103, 104: inserted

Run it twice: same final state

Here it is. Three rows in the VALUES list. The result is INSERT 0 2. Two were inserted, and the duplicate was skipped.

SQL - skip duplicates

INSERT INTO processed_events (event_id, received_at)
VALUES (101, '2026-06-02'),
       (103, '2026-06-02'),
       (104, '2026-06-02')
ON CONFLICT (event_id) DO NOTHING;

Result: INSERT 0 2

But the conflict target must match a unique index. The contacts table has no unique index on email, so Postgres rejects the statement before it runs.

SQL - no unique index

INSERT INTO contacts (contact_id, email, name)
VALUES (7, 'mei@mail.com', 'Mei Chen')
ON CONFLICT (email) DO NOTHING;

Error (PostgreSQL 16)ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

So the unique constraint is not optional. It is what makes the upsert safe. And remember that DO NOTHING skips the row for any reason it conflicts, so if you need to know which rows were skipped, add RETURNING and compare.

Interview tip

No unique index on the conflict column = no upsert. Create the constraint first.

Say this first

Add RETURNING to the INSERT. It returns the rows that the statement wrote, including generated keys and defaults. It works on UPDATE and DELETE too, so you always see exactly what changed.

Without RETURNING, it takes two trips. Insert the row, then ask the database what ID it got, and hope you ask about the right row. With RETURNING, the insert itself answers. The generated ID, the default plan, the default date: everything comes back in the same statement, and it is always your row.

Two round trips

INSERT

SELECT the new IDsecond query

App has the row

One statement

INSERT ... RETURNING *id, defaults, everything

App has the row

Insert two signups. We only give the email, and for one of them the plan. RETURNING hands back the identity values 1 and 2, the default plan free, and the default date.

SQL - insert returning

INSERT INTO signups (email, plan)
VALUES ('mei@mail.com',  'pro'),
       ('ravi@mail.com', DEFAULT)
RETURNING signup_id, email, plan, created_on;

Output (PostgreSQL 16)2 rows

signup_id	email	plan	created_on
1	mei@mail.com	pro	2026-06-02
2	ravi@mail.com	free	2026-06-02

The same clause on an UPDATE. Take 3 desks out of stock, and get the new quantity back, with an expression for the old one.

SQL - update returning

UPDATE inventory
SET qty = qty - 3
WHERE sku = 'DESK'
RETURNING sku, qty AS new_qty, qty + 3 AS old_qty;

Output (PostgreSQL 16)1 row

sku	new_qty	old_qty
DESK	2	5

RETURNING shows the row after the change. From PostgreSQL 18 you can also write old dot column and new dot column to get both versions directly. And with DELETE RETURNING inside a CTE, you can move rows to an archive table in one statement.

Interview tip

PostgreSQL 18: RETURNING old.qty, new.qty. Before that: an expression or a CTE.

Question 4: PostgreSQL 15 added the MERGE statement. A developer wants to replace every ON CONFLICT upsert with MERGE, because it is standard SQL. Is that a good idea?

Say this first

Not blindly. MERGE is more flexible: several conditions, and UPDATE, INSERT or DELETE in one statement. But only INSERT ... ON CONFLICT guarantees insert-or-update under concurrency. Two MERGEs inserting the same key can still fail with a unique violation.

Put them side by side. ON CONFLICT is driven by a unique index. It handles one case, the key already exists, and it handles it atomically. MERGE joins a source to a target, and for each row picks an action from a list of WHEN clauses. It can also delete. But MERGE decides matched or not matched from its snapshot. If another session inserts the same key in between, MERGE tries to insert, and gets a duplicate key error.

	INSERT ... ON CONFLICT	MERGE
Driven by	a unique index	a join condition (ON)
Actions	update or do nothing	update, insert, delete, per WHEN clause
Two sessions insert the same new key	safe: one inserts, one updates	one can fail: duplicate key
Available since	PostgreSQL 9.5	PostgreSQL 15
Best for	high-concurrency upserts	batch sync from a staging table

Here is MERGE doing what it is good at: syncing the inventory table from the warehouse feed. Matched rows with a different quantity are updated. Rows that are not in the target are inserted. Postgres answers MERGE 3.

SQL - merge feed

MERGE INTO inventory AS i
USING inventory_feed AS f
   ON i.sku = f.sku
WHEN MATCHED AND i.qty <> f.qty THEN
  UPDATE SET qty = f.qty, updated_on = DATE '2026-06-03'
WHEN NOT MATCHED THEN
  INSERT (sku, product, qty, updated_on)
  VALUES (f.sku, f.product, f.qty, DATE '2026-06-03');

Result: MERGE 3

And the table. LAPTOP and MOUSE were updated, LAMP is a new row, and CHAIR and DESK, which are not in the feed, were left alone.

SQL - inventory merged

SELECT * FROM inventory ORDER BY sku;

Output (PostgreSQL 16)5 rows

sku	product	qty	updated_on
CHAIR	Chair	12	2026-06-02
DESK	Desk	2	2026-06-01
LAMP	Lamp	30	2026-06-03
LAPTOP	Laptop	7	2026-06-03
MOUSE	Mouse	65	2026-06-03

So my rule: for application upserts, where many sessions write the same keys, stay with ON CONFLICT. For a nightly batch from a staging table, where you are the only writer, MERGE reads better. Newer versions add more: RETURNING, and WHEN NOT MATCHED BY SOURCE to delete missing rows, both in PostgreSQL 17.

Interview tip

App upserts → ON CONFLICT. Single-writer batch sync → MERGE.

Question 5: The contacts table has the same person several times, with emails that differ only in upper and lower case. You must delete the duplicates and keep the most recently updated row for each email. How?

Say this first

Number the rows of each email with ROW_NUMBER, newest first. Row number 1 is the keeper. Delete every row with a number above 1, by joining the numbered set back to the table. Then add a unique index so it cannot happen again.

Group the rows by lower case email, and sort each group by date, newest first. Now number them. In Sara's group, the row from May is number one. Everything numbered two or higher is a duplicate. Three rows are marked. Delete those, and each person has exactly one row left: the latest one.

contacts, numbered per email

contact_id	email	updated	rn
3	Sara@Mail.com	05-20	1
6	sara@mail.com	03-30	2
1	sara@mail.com	01-10	3
2	omar@mail.com	02-01	1
5	omar@mail.com	01-15	2
4	lina@mail.com	03-03	1

PARTITION BY lower(email)one group per person

ORDER BY updated_on DESCnewest row gets number 1

DELETE ... WHERE rn > 13 duplicates removed

Here is the delete. The subquery numbers the rows. DELETE USING joins it to the table on the primary key, and removes the rows numbered above one. RETURNING shows what was deleted.

SQL - delete duplicates

DELETE FROM contacts c
USING (
  SELECT contact_id,
         ROW_NUMBER() OVER (PARTITION BY lower(email)
                            ORDER BY updated_on DESC, contact_id DESC) AS rn
  FROM contacts
) d
WHERE c.contact_id = d.contact_id
  AND d.rn > 1
RETURNING c.contact_id, c.email, c.updated_on;

Output (PostgreSQL 16)3 rows

contact_id	email	updated_on
5	omar@mail.com	2026-01-15
6	sara@mail.com	2026-03-30
1	sara@mail.com	2026-01-10

Three rows are left, one per person, each the newest. Now close the door with a unique index on lower case email.

SQL - contacts after

SELECT * FROM contacts ORDER BY contact_id;

Output (PostgreSQL 16)3 rows

contact_id	email	name	updated_on
2	omar@mail.com	Omar Ali	2026-02-01
3	Sara@Mail.com	Sara K.	2026-05-20
4	lina@mail.com	Lina Park	2026-03-03

SQL - unique lower email

CREATE UNIQUE INDEX uq_contacts_email
ON contacts (lower(email));

Result: CREATE INDEX

On a table with no primary key at all, you can use the system column ctid, the physical address of the row, in the same pattern. And on a very large table, delete in batches, so you do not hold locks and bloat for hours.

Interview tip

No primary key? Join on ctid. Huge table? Delete in batches.

Question 6: A staging table holds new prices for some products. You need to update the products table from it. A teammate's UPDATE ran without error, but one product got the wrong price. What is the correct pattern, and what went wrong?

Say this first

Postgres joins in an UPDATE with UPDATE ... SET ... FROM ... WHERE. The trap: if the source has several rows for one target row, only one of them is used, and you cannot predict which. Make the source unique per key first.

Look at the staging rows. Product one has one new price. Fine. Product three was sent twice: 55 at eight o'clock, then 60 at nine thirty. In the UPDATE, product three joins to both rows, but a row can be updated only once per statement. Postgres uses one of the two, and does not promise which one.

products

product_id	price
1	1200.00
2	300.00
3	50.00

price_updates

product_id	new_price	loaded_at
1	1100.00	08:00
3	55.00	08:00
3	60.00	09:30

One match → clear result

Two matches → one is used,
and the choice is not defined

Fix: one source row per keykeep the latest with DISTINCT ON

Here is the safe version. A CTE keeps only the latest staging row per product, using DISTINCT ON with the newest load time first. Then UPDATE FROM joins products to that clean set. Two rows are updated.

SQL - update from latest

WITH latest AS (
  SELECT DISTINCT ON (product_id) product_id, new_price
  FROM price_updates
  ORDER BY product_id, loaded_at DESC
)
UPDATE products p
SET price = l.new_price
FROM latest l
WHERE l.product_id = p.product_id
RETURNING p.product_id, p.product, p.price;

Output (PostgreSQL 16)2 rows

product_id	product	price
1	Laptop	1100.00
3	Keyboard	60.00

The keyboard got 60, the latest price, and the laptop got 1100. The monitor was not in the feed, so it kept its price. One more trap: never forget the WHERE that joins the two tables. Without it, every product is updated, with the price of some arbitrary staging row.

Interview tip

UPDATE ... FROM without the join condition updates every row.

Question 7: A job loads a CSV file with millions of rows by sending one INSERT per row, and it runs for hours. The data must be upserted into an existing table. How do you make it fast?

Say this first

Stop working row by row. Load the file with COPY into an unlogged staging table, then run one set-based INSERT ... SELECT ... ON CONFLICT from staging into the target, and ANALYZE.

Row by row means a network round trip, a parse, and a transaction for every single row. A million rows, a million conversations. COPY sends the whole file as one stream into a staging table. No constraints to check there, and unlogged means almost no WAL. Then one statement moves everything into the real table, and handles new and existing keys together.

Row by row

INSERTrow 1

INSERTrow 2

INSERTrow 3

... a million timesround trip + commit each

Set based

COPY fileone stream

Staging tableUNLOGGED, no indexes

INSERT ... SELECT ... ON CONFLICTone statement

ANALYZE

Step one. COPY loads the CSV file into the staging table. COPY 100000: all the rows in a single command.

SQL - copy into staging

COPY stock_staging (sku, qty)
FROM '/tmp/stock_feed.csv'
WITH (FORMAT csv);

Result: COPY 100000

Step two. One INSERT SELECT with ON CONFLICT moves the 100,000 rows into the stock table. Half of them are new keys, half already exist, and one statement handles both.

SQL - upsert from staging

INSERT INTO stock (sku, qty)
SELECT sku, qty
FROM stock_staging
ON CONFLICT (sku) DO UPDATE
SET qty = EXCLUDED.qty;

Result: INSERT 0 100000

Then analyze, and check. 100,000 rows in the table: the 50,000 that existed were updated, and 50,000 new ones were inserted.

SQL - stock check

SELECT COUNT(*) AS rows_in_stock FROM stock;

Output (PostgreSQL 16)1 row

rows_in_stock
100000

From an application or from psql, use the client side version, backslash copy, or the COPY support of your driver, because server side COPY reads a file on the database server. For a first load into an empty table, also create the indexes after the data, not before.

Interview tip

Client file → \copy or the driver COPY API. Empty table → build indexes after the load.

Key points to remember

ON CONFLICT DO UPDATE is an atomic upsert. EXCLUDED is the row you tried to insert
DO NOTHING makes a load idempotent, and it needs a unique index on the conflict column
RETURNING gives back generated IDs and changed rows in the same statement
MERGE is flexible, but only ON CONFLICT is safe against concurrent inserts
Big loads: COPY into a staging table, then one set-based upsert

The full SQL script

Here is everything in one script: the sample data and every query from this post, in order.

Show the full SQL script (sample data and every query)

Full script - copy and run top to bottom

-- ========================================================================
-- PostgreSQL Interview Q&A #9 - Upserts and data changes
-- TechBrothersIT - run on PostgreSQL 16
-- ========================================================================

-- Sample data
CREATE TABLE inventory (sku TEXT PRIMARY KEY, product TEXT, qty INT, updated_on DATE);
INSERT INTO inventory VALUES
  ('LAPTOP', 'Laptop', 10, '2026-06-01'),
  ('MOUSE',  'Mouse',  50, '2026-06-01'),
  ('DESK',   'Desk',    5, '2026-06-01');

CREATE TABLE inventory_feed (sku TEXT, product TEXT, qty INT);      -- tomorrow's file from the warehouse
INSERT INTO inventory_feed VALUES ('LAPTOP', 'Laptop', 7), ('MOUSE', 'Mouse', 65), ('LAMP', 'Lamp', 30);

CREATE TABLE processed_events (event_id INT PRIMARY KEY, received_at DATE);
INSERT INTO processed_events VALUES (101, '2026-06-01'), (102, '2026-06-01');

CREATE TABLE signups (
  signup_id  INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      TEXT,
  plan       TEXT DEFAULT 'free',
  created_on DATE DEFAULT DATE '2026-06-02'
);

CREATE TABLE contacts (contact_id INT PRIMARY KEY, email TEXT, name TEXT, updated_on DATE);
INSERT INTO contacts VALUES
  (1, 'sara@mail.com', 'Sara Khan',  '2026-01-10'),
  (2, 'omar@mail.com', 'Omar Ali',   '2026-02-01'),
  (3, 'Sara@Mail.com', 'Sara K.',    '2026-05-20'),   -- same person, newer row
  (4, 'lina@mail.com', 'Lina Park',  '2026-03-03'),
  (5, 'omar@mail.com', 'Omar A.',    '2026-01-15'),   -- same person, older row
  (6, 'sara@mail.com', 'S. Khan',    '2026-03-30');

CREATE TABLE products (product_id INT PRIMARY KEY, product TEXT, price NUMERIC(10,2));
INSERT INTO products VALUES (1, 'Laptop', 1200.00), (2, 'Monitor', 300.00), (3, 'Keyboard', 50.00);

CREATE TABLE price_updates (product_id INT, new_price NUMERIC(10,2), loaded_at TIMESTAMP);
INSERT INTO price_updates VALUES
  (1, 1100.00, '2026-06-02 08:00'),
  (3,   55.00, '2026-06-02 08:00'),
  (3,   60.00, '2026-06-02 09:30');     -- product 3 was sent twice

CREATE TABLE stock (sku TEXT PRIMARY KEY, qty INT);
INSERT INTO stock SELECT 'SKU-' || g, 0 FROM generate_series(1, 50000) AS g;   -- 50,000 existing rows
CREATE UNLOGGED TABLE stock_staging (sku TEXT, qty INT);

-- Step 1 (question 1): upsert inventory
INSERT INTO inventory (sku, product, qty, updated_on)
VALUES ('LAPTOP', 'Laptop',  8, '2026-06-02'),
       ('CHAIR',  'Chair',  12, '2026-06-02')
ON CONFLICT (sku) DO UPDATE
SET qty        = EXCLUDED.qty,
    updated_on = EXCLUDED.updated_on;

-- Step 2 (question 1): inventory after
SELECT * FROM inventory ORDER BY sku;

-- Step 3 (question 2): skip duplicates
INSERT INTO processed_events (event_id, received_at)
VALUES (101, '2026-06-02'),
       (103, '2026-06-02'),
       (104, '2026-06-02')
ON CONFLICT (event_id) DO NOTHING;

-- Step 4 (question 2): no unique index
INSERT INTO contacts (contact_id, email, name)
VALUES (7, 'mei@mail.com', 'Mei Chen')
ON CONFLICT (email) DO NOTHING;

-- Step 5 (question 3): insert returning
INSERT INTO signups (email, plan)
VALUES ('mei@mail.com',  'pro'),
       ('ravi@mail.com', DEFAULT)
RETURNING signup_id, email, plan, created_on;

-- Step 6 (question 3): update returning
UPDATE inventory
SET qty = qty - 3
WHERE sku = 'DESK'
RETURNING sku, qty AS new_qty, qty + 3 AS old_qty;

-- Step 7 (question 4): merge feed
MERGE INTO inventory AS i
USING inventory_feed AS f
   ON i.sku = f.sku
WHEN MATCHED AND i.qty <> f.qty THEN
  UPDATE SET qty = f.qty, updated_on = DATE '2026-06-03'
WHEN NOT MATCHED THEN
  INSERT (sku, product, qty, updated_on)
  VALUES (f.sku, f.product, f.qty, DATE '2026-06-03');

-- Step 8 (question 4): inventory merged
SELECT * FROM inventory ORDER BY sku;

-- Step 9 (question 5): delete duplicates
DELETE FROM contacts c
USING (
  SELECT contact_id,
         ROW_NUMBER() OVER (PARTITION BY lower(email)
                            ORDER BY updated_on DESC, contact_id DESC) AS rn
  FROM contacts
) d
WHERE c.contact_id = d.contact_id
  AND d.rn > 1
RETURNING c.contact_id, c.email, c.updated_on;

-- Step 10 (question 5): contacts after
SELECT * FROM contacts ORDER BY contact_id;

-- Step 11 (question 5): unique lower email
CREATE UNIQUE INDEX uq_contacts_email
ON contacts (lower(email));

-- Step 12 (question 6): update from latest
WITH latest AS (
  SELECT DISTINCT ON (product_id) product_id, new_price
  FROM price_updates
  ORDER BY product_id, loaded_at DESC
)
UPDATE products p
SET price = l.new_price
FROM latest l
WHERE l.product_id = p.product_id
RETURNING p.product_id, p.product, p.price;

-- Step 13 (question 7): copy into staging
COPY stock_staging (sku, qty)
FROM '/tmp/stock_feed.csv'
WITH (FORMAT csv);

-- Step 14 (question 7): upsert from staging
INSERT INTO stock (sku, qty)
SELECT sku, qty
FROM stock_staging
ON CONFLICT (sku) DO UPDATE
SET qty = EXCLUDED.qty;

-- Step 15 (question 7): stock check
SELECT COUNT(*) AS rows_in_stock FROM stock;

Next in this series

Part 10: Partitioning, VACUUM and maintenance

The sample data on this page is invented for teaching. Every output table and query plan is the real result of the query above it, run on PostgreSQL 16 over that sample data.

Welcome To TechBrothersIT

Label

PostgreSQL Interview Questions and Answers Part 9 - Upserts and data changes

Let's create some sample data

Question 1: A nightly feed sends inventory rows. If the SKU is new, the row must be inserted. If the SKU already exists, its quantity must be updated. How do you do this upsert in one statement in PostgreSQL?

Question 2: A message queue sometimes delivers the same event twice. The loader must skip events it has already stored, without an error and without checking first. How do you write the insert?

Question 4: PostgreSQL 15 added the MERGE statement. A developer wants to replace every ON CONFLICT upsert with MERGE, because it is standard SQL. Is that a good idea?

Question 5: The contacts table has the same person several times, with emails that differ only in upper and lower case. You must delete the duplicates and keep the most recently updated row for each email. How?

Question 6: A staging table holds new prices for some products. You need to update the products table from it. A teammate's UPDATE ran without error, but one product got the wrong price. What is the correct pattern, and what went wrong?

Question 7: A job loads a CSV file with millions of rows by sending one INSERT per row, and it runs for hours. The data must be upserted into an existing table. How do you make it fast?

Key points to remember

The full SQL script

No comments:

Post a Comment

Label

PostgreSQL Interview Questions and Answers Part 9 - Upserts and data changes

Let's create some sample data

Question 1: A nightly feed sends inventory rows. If the SKU is new, the row must be inserted. If the SKU already exists, its quantity must be updated. How do you do this upsert in one statement in PostgreSQL?

Question 2: A message queue sometimes delivers the same event twice. The loader must skip events it has already stored, without an error and without checking first. How do you write the insert?

Question 3: After inserting a signup, the application needs the generated ID and the default values, and today it runs a second SELECT to get them. How do you avoid the second round trip?

Question 4: PostgreSQL 15 added the MERGE statement. A developer wants to replace every ON CONFLICT upsert with MERGE, because it is standard SQL. Is that a good idea?

Question 5: The contacts table has the same person several times, with emails that differ only in upper and lower case. You must delete the duplicates and keep the most recently updated row for each email. How?

Question 6: A staging table holds new prices for some products. You need to update the products table from it. A teammate's UPDATE ran without error, but one product got the wrong price. What is the correct pattern, and what went wrong?

Question 7: A job loads a CSV file with millions of rows by sending one INSERT per row, and it runs for hours. The data must be upserted into an existing table. How do you make it fast?

Key points to remember

The full SQL script

No comments:

Post a Comment