How to Use Transactions in PostgreSQL: Syntax and Examples

In PostgreSQL, use BEGIN to start a transaction, execute your SQL commands, then use COMMIT to save changes or ROLLBACK to undo them. Transactions ensure your operations are all done together or not at all, keeping data safe and consistent.

Syntax

A transaction in PostgreSQL starts with BEGIN, followed by one or more SQL statements. You end the transaction with COMMIT to save changes or ROLLBACK to cancel them. This ensures all statements inside the transaction are treated as a single unit.

• BEGIN; - starts the transaction
• COMMIT; - saves all changes made during the transaction
• ROLLBACK; - cancels all changes made during the transaction

sql

BEGIN;
-- your SQL statements here
COMMIT;

Example

This example shows a transaction that transfers money between two accounts. If any step fails, the transaction rolls back to keep data consistent.

sql

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

Output

UPDATE 1
UPDATE 1
COMMIT

Common Pitfalls

Common mistakes include forgetting to COMMIT or ROLLBACK, which leaves the transaction open and locks data. Also, mixing transactions with autocommit mode can cause unexpected results. Always ensure transactions are properly closed.

sql

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- forgot COMMIT or ROLLBACK here

-- Correct way:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
ROLLBACK;

Quick Reference

Command	Purpose
BEGIN;	Start a new transaction
COMMIT;	Save all changes made in the transaction
ROLLBACK;	Undo all changes made in the transaction
SAVEPOINT name;	Create a savepoint to rollback part of a transaction
ROLLBACK TO SAVEPOINT name;	Rollback to a savepoint without ending the transaction

Key Takeaways

Always start a transaction with BEGIN and end with COMMIT or ROLLBACK.

Transactions group multiple SQL statements to ensure data consistency.

Forgetting to commit or rollback leaves transactions open and locks data.

Use savepoints to rollback parts of a transaction if needed.

Test transactions carefully to avoid partial updates or deadlocks.