Update data ​

UPDATE changes existing rows selected by WHERE. Expressions can read the old row values, so an assignment can increment or decrement a stored amount.

The resulting values are checked against declared types and constraints. Changes complete synchronously and can be grouped in a transaction.

Example ​

CREATE TABLE stock(id INTEGER PRIMARY KEY, quantity INTEGER);
INSERT INTO stock VALUES (1, 5);

UPDATE stock
SET quantity = quantity - 1
WHERE id = 1
RETURNING id, quantity
FORMAT csv;

1,4

Without WHERE, an update changes every row. RETURNING exposes the updated values.

Upsert ​

Use INSERT ... ON CONFLICT ... DO UPDATE when the operation should insert a new row or update an existing conflicting row. This is separate from an ordinary UPDATE, which never inserts a missing row.

See UPDATE, INSERT INTO and transactions.