Home›Postgres›Postgres – INSERT, UPDATE, DELETE and Upsert

Postgres – INSERT, UPDATE, DELETE and Upsert

September 29, 20196 min readUpdated 8/23/2026

Reading is where SQL tutorials spend their time and writing is where the bugs are. This post is the four statements that change data, plus the clauses that stop you needing a second round trip or a lock.

INSERT, and RETURNING

INSERT INTO amenities (slug, name, icon)
VALUES ('sauna', 'Sauna', 'flame')
RETURNING id, slug, created_at;

RETURNING is the clause worth adopting first. Without it, inserting a row with a generated key means a second query to find out what the key was — and the naive version of that second query (SELECT max(id)) is wrong under concurrency. RETURNING gives you the row that was actually written, including defaults and generated columns, in the same round trip. It works on UPDATE and DELETE too.

Insert many rows in one statement rather than many:

INSERT INTO amenities (slug, name, icon) VALUES
    ('piano',   'Piano',   'music'),
    ('kayak',   'Kayak',   'waves'),
    ('hammock', 'Hammock', 'palmtree')
RETURNING id, slug;

One statement, one round trip, one WAL flush. Inserting a thousand rows in a thousand statements is slower by more than a factor of a thousand, because each one pays a network round trip and a commit.

Upsert

ON CONFLICT turns "insert it, or update it if it is already there" into one statement with no race between the check and the write:

INSERT INTO amenities (slug, name, icon)
VALUES ('sauna', 'Sauna', 'flame')
ON CONFLICT (slug) DO UPDATE
    SET name = EXCLUDED.name,
        icon = EXCLUDED.icon,
        updated_at = now()
RETURNING id, slug, name;

Three things to know:

  • EXCLUDED is the row you tried to insert. The bare column names refer to the row already in the table, so SET name = EXCLUDED.name means "take the new value".
  • The conflict target needs a unique constraint or index. ON CONFLICT (slug) works because uq_amenities_slug exists. Without one, Postgres cannot tell what "conflict" means and rejects the statement.
  • DO NOTHING is the other option, and it swallows every conflict on the table, not only the one you were thinking of. Name the target unless you genuinely mean any.
-- only overwrite when something actually changed
INSERT INTO amenities (slug, name, icon)
VALUES ('sauna', 'Sauna', 'flame')
ON CONFLICT (slug) DO UPDATE
    SET name = EXCLUDED.name, updated_at = now()
WHERE  amenities.name IS DISTINCT FROM EXCLUDED.name
RETURNING id;

That WHERE is worth adding on any upsert that runs often. Without it, every run writes every row — new row versions, new index entries, more work for autovacuum — even when nothing changed. With it, an unchanged row returns nothing and costs nothing.

UPDATE, and UPDATE ... FROM

UPDATE bookings
SET    status = 'CANCELLED',
       cancelled_at = now(),
       cancellation_reason = 'guest request'
WHERE  id = 12345
RETURNING id, status, cancelled_at;

To set values from another table, Postgres has UPDATE ... FROM — a join in an update:

UPDATE properties p
SET    rating_average = s.avg_rating,
       rating_count   = s.n
FROM  (SELECT property_id, round(avg(rating), 2) AS avg_rating, count(*) AS n
       FROM   reviews GROUP BY property_id) s
WHERE  p.id = s.property_id
  AND  (p.rating_average, p.rating_count) IS DISTINCT FROM (s.avg_rating, s.n);

The last line is the same idea again: touch only the rows whose values would change. On a table of 20,000 properties it turns a full rewrite into a handful of updated rows.

DELETE ... USING is the same shape for deletes. Both are Postgres extensions and both are much faster than the correlated-subquery version.

DELETE, and why production rarely does

DELETE FROM outbox
WHERE  status = 'DONE' AND created_at < now() - INTERVAL '30 days'
RETURNING id;

Most rows in a production database should never be deleted. The booking schema carries a deleted boolean on properties and users for that reason — a soft delete keeps foreign keys valid, keeps history readable, and is reversible when somebody asks.

The cost is that every query must remember it, which is a real cost. A view is the usual answer:

CREATE OR REPLACE VIEW live_properties AS
    SELECT * FROM properties WHERE NOT deleted;

When you do delete, delete in batches. A single DELETE of ten million rows is one transaction holding locks for its whole duration, and it produces ten million dead row versions for autovacuum to deal with at once:

DELETE FROM outbox
WHERE  id IN (SELECT id FROM outbox
              WHERE  status = 'DONE' AND created_at < now() - INTERVAL '30 days'
              LIMIT  10000);

Loop that until it reports zero rows. Each batch commits, releases its locks, and lets autovacuum keep up.

TRUNCATE is the exception that empties a table in constant time — it drops the underlying files instead of marking rows dead. It takes an exclusive lock, cannot be filtered, and does not fire row triggers.

Locks, and the order rows are touched in

An UPDATE or DELETE takes a row-level lock on everything it changes, held until the transaction commits. Two things follow, and both cause outages rather than errors.

A statement that changes many rows blocks every writer of those rows for its whole duration. Readers are unaffected — that is MVCC doing its job — but a backfill over a million rows is a million locked rows, and any request that touches one waits. Batching is the answer, and it is the same batching as for deletes.

Two statements touching the same rows in different orders deadlock. Postgres detects it in about a second, kills one transaction and reports deadlock detected. The fix is never a retry loop on its own — it is making every writer touch rows in the same order:

-- deterministic order: two concurrent runs of this cannot deadlock against each other
UPDATE bookings SET status = 'COMPLETED'
WHERE  id IN (SELECT id FROM bookings
              WHERE  status = 'CONFIRMED' AND check_out < DATE '2024-03-01'
              ORDER  BY id
              LIMIT  5000);

ORDER BY id in the subquery is doing the work. Without it two batches can pick overlapping sets in opposite orders, and the deadlock appears under load and never in testing.

Bulk loading with COPY

psql "$DATABASE_URL" -c "\copy amenities (slug, name, icon) FROM 'amenities.csv' CSV HEADER"

COPY is an order of magnitude faster than INSERT for bulk data: one statement, one parse, minimal per-row overhead. Every language driver exposes it — in Python, psycopg's copy(); in Java, the JDBC driver's CopyManager.

Two habits for a large load: drop non-essential indexes first and rebuild them afterwards, since maintaining an index per row is most of the cost, and ANALYZE when you are done so the planner knows what arrived.

Everything above is already in a transaction

A statement sent on its own runs in its own transaction and commits when it succeeds. That is why a DELETE with a mistyped WHERE is final the moment you press enter — and why the habit that saves you is one line:

BEGIN;
UPDATE bookings SET status = 'CANCELLED' WHERE property_id = 42;
-- read the row count. If it is not what you expected: ROLLBACK;
COMMIT;

In an application the same protection comes from the framework's transaction boundary, and the thing to check is where it is drawn. A web request that opens a transaction per statement gets the behaviour above — each write final on its own — while one transaction per request means a failure half way through leaves nothing behind. The second is almost always what you want, and it is a configuration decision rather than something the SQL tells you.

The transactions post later in this track is about what that BEGIN actually promises, and what it does not.