Catalog My learning Blog
PostgreSQL Course: Beginner to Production in 30 Days · Phase 1 · Lesson 6 · 7 min read

PostgreSQL INSERT, UPDATE, DELETE and UPSERT

On this page
  1. The UPDATE without a WHERE clause
  2. INSERT: adding rows
  3. Getting the new id back with RETURNING
  4. Updating and deleting without regret
  5. Upsert with INSERT ... ON CONFLICT
  6. Syncing a table with MERGE
  7. Loading files with COPY
  8. Emptying a table: DELETE or TRUNCATE?
  9. Check yourself

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.

SQL
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:

SQL
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:

SQL
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:

SQL
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:

SQL
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:

SQL
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:

Code
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:

SQL
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:

SQL
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:

SQL
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:

SQL
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:

DELETETRUNCATE
WHERE clauseYesNo, it's all or nothing
Speed on big tablesSlow, it touches every rowNear instant, it swaps in empty files
Row-level triggersFireDon't fire
LockNormal row locks, readers carry onExclusive lock, blocks even reads briefly
RollbackYesYes, 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.

Code
TRUNCATE department_archive;
TRUNCATE department_archive RESTART IDENTITY;   -- also reset identity counters

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