Transactions (BEGIN, COMMIT, ROLLBACK)

Group several statements so they succeed or fail together.

Syntax

BEGIN;
  statement; statement; ...
  [SAVEPOINT sp;  ...  ROLLBACK TO SAVEPOINT sp;]
COMMIT;      -- or ROLLBACK;

A transaction is atomic (all or nothing), isolated (others do not see uncommitted changes) and durable once committed.

Without BEGIN, each statement is its own transaction (autocommit).
After any error inside a transaction block, PostgreSQL rejects further statements with current transaction is aborted, commands ignored until end of transaction block until you ROLLBACK (or roll back to a savepoint).
SAVEPOINT marks a point you can roll back to without abandoning the whole transaction.
DDL is transactional in PostgreSQL: CREATE TABLE, ALTER TABLE and DROP can be rolled back.
Default isolation is READ COMMITTED: each statement sees data committed before it started. REPEATABLE READ / SERIALIZABLE give a stable snapshot for the whole transaction.
Keep transactions short: open transactions hold locks.

Examples

Try a change, then undo it (modifies data, rolled back) starter

BEGIN;
DELETE FROM orders WHERE status = 'cancelled';
SELECT count(*) AS orders_inside_transaction FROM orders;
ROLLBACK;
SELECT count(*) AS orders_after_rollback FROM orders;

The count drops inside the transaction and is back to the original after ROLLBACK.

Savepoint (modifies data) starter

BEGIN;
UPDATE products SET stock = stock + 10 WHERE product_id = 1;
SAVEPOINT before_price;
UPDATE products SET price = price * 2;
ROLLBACK TO SAVEPOINT before_price;
COMMIT;
SELECT product_id, price, stock FROM products WHERE product_id IN (1, 2) ORDER BY product_id;

The stock change is kept; the accidental price doubling is undone.

Pitfalls

Continuing to send statements after an error inside BEGIN (they are all rejected until ROLLBACK).
Leaving a transaction open (locks, and in some tools your changes are never committed).
Assuming DDL cannot be rolled back (it can in PostgreSQL, unlike MySQL and Oracle).

Other databases

For orientation only: SQL Quest executes PostgreSQL.

SQL Server: BEGIN TRANSACTION / COMMIT / ROLLBACK; SAVE TRANSACTION name for savepoints. Many run-time errors do not roll back the transaction unless SET XACT_ABORT ON is used.
MySQL: START TRANSACTION or BEGIN; DDL statements cause an implicit commit.
Oracle: A transaction starts implicitly; DDL commits implicitly.