Writing data in transactions

In plain English

INSERT adds rows, UPDATE changes existing rows, and DELETE removes rows. RETURNING lets any of these statements send back the rows they changed, so you can read the result without a second query. A transaction groups several statements into one all-or-nothing unit: BEGIN starts it, COMMIT saves everything, and ROLLBACK undoes it. A savepoint is a named marker inside a transaction that you can roll back to without discarding the whole transaction.

Step by step

1. 1Start a transaction with begin.
2. 2Insert a row and read its id with returning.
3. 3Update rows using a where clause to target them.
4. 4Mark a savepoint and roll back to it if a later step goes wrong.
5. 5Finish with commit to keep the changes, or rollback to discard them.

begin;

insert into courses (slug, title)
values ('postgresql', 'PostgreSQL Foundations')
returning id;

savepoint inserted;

update courses
set title = 'PostgreSQL Foundations: Revised'
where slug = 'postgresql';

rollback to savepoint inserted;

commit;

Common mistakes

• Running an UPDATE or DELETE without a WHERE clause changes every row in the table, which is rarely what you intended.
• Forgetting to commit leaves a transaction open, which can hold locks and block other work.
• Assuming an error automatically saves the good parts is wrong, because in PostgreSQL an error aborts the whole transaction until you roll back.
• Expecting DELETE ... RETURNING to show what remains is a mistake, because it returns the rows that were deleted.