PostgreSQL INSERT, UPDATE, DELETE and UPSERT
On this page
The UPDATE without a WHERE clause
Almost every developer has run an UPDATE or DELETE and then realized the WHERE clause was missing. Every row changed. This lesson covers INSERT, UPDATE and DELETE, plus the habits that make that mistake hard to make.
The examples use the departments and employees tables from Day 5.
INSERT: adding rows
Always name the columns you're inserting into. If you rely on column order, the statement breaks, or worse, quietly puts values in the wrong columns, the day someone adds a column.
INSERT INTO departments (name) VALUES ('Engineering');
INSERT INTO departments (name) VALUES
('Sales'),
('Marketing'),
('Human Resources');The second form inserts several rows in one statement. It's much faster than separate INSERTs, because it saves a network round trip and a parse per row.
You can also insert the result of a query. That's handy for archiving or copying data between tables:
CREATE TABLE department_archive (name text, archived_at timestamptz DEFAULT now());
INSERT INTO department_archive (name)
SELECT name FROM departments WHERE name = 'Human Resources';Getting the new id back with RETURNING
After inserting a row you usually need its generated id. Instead of running a second query, add RETURNING:
INSERT INTO departments (name)
VALUES ('Finance')
RETURNING id, name;RETURNING works on UPDATE and DELETE too, so you can see exactly which rows changed. Most drivers and ORMs use it behind the scenes to fetch generated keys.
Since PostgreSQL 18, an UPDATE can return the row as it was and as it is now, which is handy for audit messages and "price changed from X to Y" notifications:
UPDATE employees SET salary = salary * 1.05
WHERE email = 'ben.ortiz@example.com'
RETURNING old.salary AS before, new.salary AS after;Updating and deleting without regret
The WHERE clause decides which rows change. Leave it out and every row changes. Two habits protect you.
Run the WHERE as a SELECT first. Before changing anything, check what matches:
SELECT id, email FROM employees WHERE status = 'terminated';Wrap risky changes in a transaction. psql shows how many rows were affected. If the number is wrong, roll back:
BEGIN;
UPDATE employees SET salary = salary * 1.05
WHERE department_id = (SELECT id FROM departments WHERE name = 'Engineering');
-- psql prints UPDATE 3. Expected 3? Then COMMIT. Otherwise:
ROLLBACK;You can update several columns at once, and use another table to decide what to change with UPDATE ... FROM:
UPDATE employees e
SET department_id = d.id
FROM departments d
WHERE d.name = 'Sales' AND e.email = 'ben.ortiz@example.com';DELETE follows the same pattern. Adding RETURNING gives you a record of what was removed:
DELETE FROM employees
WHERE status = 'terminated' AND hired_at < now() - INTERVAL '2 years'
RETURNING id, email;Upsert with INSERT ... ON CONFLICT
Say you keep a headcount per department. When a new number arrives, you want to insert a row if none exists, or update it if one does. This is called an upsert.
The naive way is to SELECT, then decide between INSERT and UPDATE. It breaks under load. Two requests can both see "no row" and both try to insert. PostgreSQL's INSERT ... ON CONFLICT does the check and the write as one atomic step:
CREATE TABLE department_totals (
department_id int PRIMARY KEY,
headcount int NOT NULL DEFAULT 0
);
-- Insert, or add to the existing count
INSERT INTO department_totals (department_id, headcount)
VALUES (1, 10)
ON CONFLICT (department_id)
DO UPDATE SET headcount = department_totals.headcount + EXCLUDED.headcount;
-- Insert, or silently skip if the row exists
INSERT INTO department_totals (department_id, headcount)
VALUES (1, 10)
ON CONFLICT (department_id) DO NOTHING;EXCLUDED is the row you tried to insert. department_totals.headcount is the row already there. Having both lets you merge them however you like.
ON CONFLICT needs a unique constraint or unique index on the conflict columns. Without one, PostgreSQL has no way to know what counts as a duplicate, and the statement fails.
Syncing a table with MERGE
ON CONFLICT handles one decision: insert, or update the row that's already there. Sometimes a nightly feed needs more. Update the departments that changed, add new ones, and remove ones whose count dropped to zero. MERGE (PostgreSQL 15 and later) does all of it in one statement:
INSERT INTO department_totals VALUES (3, 4); -- a second department to work with
CREATE TABLE headcount_feed (department_id int PRIMARY KEY, headcount int NOT NULL);
INSERT INTO headcount_feed VALUES (1, 12), (2, 7), (3, 0);
MERGE INTO department_totals t
USING headcount_feed f ON f.department_id = t.department_id
WHEN MATCHED AND f.headcount = 0 THEN
DELETE
WHEN MATCHED THEN
UPDATE SET headcount = f.headcount
WHEN NOT MATCHED THEN
INSERT (department_id, headcount) VALUES (f.department_id, f.headcount);Starting from totals of 10 for department 1 and 4 for department 3, the result is department 1 updated to 12, department 2 inserted with 7, and department 3 deleted. The WHEN clauses are checked in order and the first one that matches wins, which is why the zero check comes before the plain update.
PostgreSQL 17 added two more pieces. WHEN NOT MATCHED BY SOURCE THEN DELETE removes target rows that are missing from the feed, which turns MERGE into a full sync. And RETURNING merge_action(), t.* reports what happened to each row (INSERT, UPDATE or DELETE).
One caution: MERGE isn't a replacement for ON CONFLICT when many sessions upsert the same keys at once. Two concurrent MERGEs can both decide "not matched" and the second insert then fails with a duplicate-key error. For concurrent single-row upserts, keep using INSERT ... ON CONFLICT. Use MERGE for batch syncs from a staging table, where one job runs at a time.
Loading files with COPY
Multi-row INSERT is fine for hundreds of rows. For a CSV with a million, use COPY, which streams rows straight into the table and is many times faster than any INSERT:
CREATE TABLE staging_departments (name text);
\copy staging_departments FROM 'depts.csv' WITH (FORMAT csv, HEADER true)
\copy (SELECT id, name FROM departments ORDER BY id) TO 'departments_out.csv' WITH (FORMAT csv, HEADER true)Mind the backslash. \copy is a psql meta-command that reads and writes files on your machine and sends the data over the connection. Plain COPY ... FROM '/path/file.csv' runs on the server and reads the server's disk, so it needs a file the server can see and a role with pg_read_server_files or superuser. From application code, drivers expose the same COPY ... FROM STDIN protocol that \copy uses.
A good pattern for messy files: COPY into a staging table with loose text columns, check and clean the rows with SQL, then INSERT ... SELECT (or MERGE) into the real table.
Emptying a table: DELETE or TRUNCATE?
Both can remove every row. They work very differently:
| DELETE | TRUNCATE | |
|---|---|---|
| WHERE clause | Yes | No, it's all or nothing |
| Speed on big tables | Slow, it touches every row | Near instant, it swaps in empty files |
| Row-level triggers | Fire | Don't fire |
| Lock | Normal row locks, readers carry on | Exclusive lock, blocks even reads briefly |
| Rollback | Yes | Yes, inside a transaction |
That last row surprises people coming from other databases. In PostgreSQL, TRUNCATE is transactional. Run it inside BEGIN and you can still roll it back.
TRUNCATE department_archive;
TRUNCATE department_archive RESTART IDENTITY; -- also reset identity countersUse TRUNCATE to clear staging tables between batch loads. Use DELETE with a WHERE clause for everything else.
Check yourself
Run the WHERE clause as a SELECT and check the count. Then run the DELETE inside BEGIN, confirm psql reports 400 rows, and COMMIT, or ROLLBACK if the number is off.
Two requests can both see no row before either inserts. Replace the pair with INSERT ... ON CONFLICT (email) DO NOTHING (or DO UPDATE), which checks and writes atomically.
There's no unique constraint or unique index on sku. ON CONFLICT can only detect duplicates that a unique index defines, because that index is what lets PostgreSQL check for a conflict atomically. Create one with CREATE UNIQUE INDEX ON products (sku); or a UNIQUE constraint, then the upsert works.
Plain COPY reads the file on the database server, not on your machine. Use psql's \copy, which reads the local file and sends it over the connection.
When you want to remove every row from a large table quickly. TRUNCATE drops the data files instead of deleting row by row, so it's nearly instant and leaves no dead rows for vacuum. But it takes a strong lock, doesn't fire row-level triggers, and can't target a subset of rows.