Writing, and hearing back

Change, and get the row back

A real application rarely writes a row and moves on: it needs the id the database chose, or the values it ended up with after a trigger or a default. RETURNING is PostgreSQL's answer — INSERT INTO ... RETURNING id hands the row back as if the statement had been a SELECT. The same suffix works on UPDATE and DELETE, which is how a program knows exactly what it changed.

The reports in the output pane tell the same story: a write says how many rows it affected, and with RETURNING it also shows them. Two rows affected when you expected one is the shortest bug report you will ever receive.

A write that returns no rows is a result too. If UPDATE ... WHERE sku = 'desk-1' RETURNING * comes back empty, the statement succeeded and matched nothing; the usual cause is a key that is off by one character, not a broken database. Reading the count before trusting the change is the habit RETURNING exists to make easy.

The upsert

Insert-or-update is so common that PostgreSQL gave it syntax: INSERT ... ON CONFLICT (sku) DO UPDATE SET price = EXCLUDED.price. The conflict target — the column or columns in brackets — must have a uniqueness rule behind it, which is what makes "already there" a precise question rather than a guess. EXCLUDED is the row the INSERT was trying to write.

DO NOTHING is the other half: useful when the second arrival should be ignored entirely, like a duplicate webhook or a replayed import whose first copy was the real one. Both forms run inside one statement, which means two processes racing to write the same key cannot both win.

Deleting, carefully

DELETE FROM ... WHERE ... removes rows that match, and the WHERE clause is the part that matters: a DELETE without one empties the table, and a database that assumes the clause was meant will not stop you. The habit worth forming on the first day is to write the WHERE clause, then run the SELECT with the same clause and read the rows before turning it into a DELETE.

In the sandbox this is free — every run rebuilds the data — which is exactly why it is a good place to practise the sequence: select, read, delete, and check with RETURNING that the right rows went.

A write makes a new version

PostgreSQL uses multi-version concurrency control: an UPDATE does not overwrite the row in place. It writes a new version and marks the old one as superseded, so a reader that started earlier keeps seeing a consistent snapshot. The old version is reclaimed later, when no transaction can still need it.

This is why RETURNING shows the row as it now is, including defaults and the result of the expression in SET, while an old snapshot would still see the previous version. It is also why a long-running transaction can hold back cleanup and make a busy table grow — a detail worth knowing before leaving a report open for an hour.

The conflict check is part of the statement

INSERT ... ON CONFLICT is one statement, not a select followed by an insert. The uniqueness check and the write happen under the same lock, so two clients trying to create the same sku cannot both decide they were first. One inserts, the other takes the DO UPDATE branch, and both get a row back.

EXCLUDED is the proposed row — the values the INSERT was carrying. In DO UPDATE, EXCLUDED.price is the incoming price and the bare price is the value already stored, which is exactly the distinction an upsert needs for "replace" versus "add to".

Try these next

• Run an UPDATE that changes nothing and watch the affected-row count.
• Change the upsert to add the incoming quantity to the stored one instead of replacing it.
• Delete a row with RETURNING * and compare the values with what the table held before.

Writes that answer

CREATE TABLE stock (
    sku text PRIMARY KEY,
    name text NOT NULL,
    quantity integer NOT NULL DEFAULT 0,
    price numeric(10, 2) NOT NULL
);

-- The row that comes back is the row that was written —
-- including the default that was filled in.
INSERT INTO stock (sku, name, price) VALUES
    ('desk-01', 'Oak Desk', 249.00)
RETURNING sku, quantity, price;

-- A change, and the before/after shown side by side.
UPDATE stock
SET price = price + 10
WHERE sku = 'desk-01'
RETURNING name, price;

-- Insert, or update if that sku is already there.
INSERT INTO stock (sku, name, quantity, price) VALUES
    ('desk-01', 'Oak Desk', 5, 259.00),
    ('lamp-02', 'Desk Lamp', 12, 24.50)
ON CONFLICT (sku) DO UPDATE
SET quantity = EXCLUDED.quantity,
    price = EXCLUDED.price
RETURNING sku, quantity, price;

-- The second arrival of a delivered event is simply ignored.
INSERT INTO stock (sku, name, price) VALUES
    ('lamp-02', 'Desk Lamp', 24.50)
ON CONFLICT (sku) DO NOTHING;

-- Delete, and prove which rows left.
DELETE FROM stock WHERE quantity = 0 RETURNING sku;

SELECT sku, name, quantity, price FROM stock ORDER BY sku;