The essentials
Quick reference
One focused task per row. Jump to the related section for complete, working examples.
| Use | Syntax | Examples |
|---|---|---|
| Insert one row | INSERT INTO customers (name, email)
VALUES ('Ada', 'ada@example.com'); | View examples |
| Insert several rows | INSERT INTO tags (name)
VALUES ('sql'), ('database'), ('backend'); | View examples |
| Insert query results | INSERT INTO customer_archive (id, name)
SELECT id, name
FROM customers
WHERE inactive = true; | View examples |
| Use a column default | INSERT INTO jobs (name, status)
VALUES ('reindex', DEFAULT); | View examples |
| Update matching rows | UPDATE customers
SET active = false
WHERE last_seen_at < DATE '2025-01-01'; | View examples |
| Update from the old value | UPDATE products
SET price = price * 1.05
WHERE category = 'books'; | View examples |
| Update from another table | UPDATE products AS p
SET price = i.price
FROM imports AS i
WHERE i.sku = p.sku; | View examples |
| Delete matching rows | DELETE FROM sessions
WHERE expires_at < CURRENT_TIMESTAMP; | View examples |
| Delete using another table | DELETE FROM cart_items AS ci
USING products AS p
WHERE p.id = ci.product_id AND p.discontinued = true; | View examples |
| Ignore a duplicate insert | INSERT INTO tags (name)
VALUES ('sql')
ON CONFLICT (name)
DO NOTHING; | View examples |
| Upsert a row | INSERT INTO counters (key, value)
VALUES ('views', 1)
ON CONFLICT (key)
DO UPDATE SET value = counters.value + EXCLUDED.value; | View examples |
| Return changed values | INSERT INTO customers (name)
VALUES ('Ada')
RETURNING id, name; | View examples |
| Begin a transaction | BEGIN; | View examples |
| Commit a transaction | COMMIT; | View examples |
| Roll back a transaction | ROLLBACK; | View examples |
| Create a savepoint | SAVEPOINT before_optional_step; | View examples |
| Roll back to a savepoint | ROLLBACK TO SAVEPOINT before_optional_step; | View examples |
Treat data changes as explicit, reviewable operations. Name inserted columns, test the predicates used by updates and deletes, return affected identifiers when useful, and group related statements in a transaction.
Step by step
Detailed examples
Insert explicit and query-derived rows
Name the destination columns so schema changes do not silently alter value placement. A VALUES list handles literal rows, INSERT SELECT copies query results, and DEFAULT requests the target column's configured default.
INSERT INTO customers (name, email)
VALUES ('Ada', 'ada@example.com');
INSERT INTO tags (name)
VALUES ('sql'), ('database'), ('backend');INSERT INTO customer_archive (id, name)
SELECT id, name
FROM customers
WHERE inactive = true;
INSERT INTO jobs (name, status)
VALUES ('reindex', DEFAULT);Note: The selected expressions must align with the target columns by position and use compatible types.
Update only reviewed target rows
An UPDATE without WHERE affects every row. First run a SELECT with the intended predicate, then reuse that predicate in the update. PostgreSQL's UPDATE FROM syntax can draw values from another table, but the join should produce at most one source row for each target.
SELECT id, last_seen_at
FROM customers
WHERE last_seen_at < DATE '2025-01-01';
UPDATE customers
SET active = false
WHERE last_seen_at < DATE '2025-01-01';UPDATE products
SET price = price * 1.05
WHERE category = 'books';
UPDATE products AS p
SET price = i.price
FROM imports AS i
WHERE i.sku = p.sku;Note: UPDATE FROM is PostgreSQL syntax. Other database systems use different joined-update forms.
Delete with a precise predicate
DELETE without WHERE removes every row from the target table. Preview the exact predicate with SELECT and use a transaction for important changes. PostgreSQL USING makes other tables available to the delete condition.
SELECT id, expires_at
FROM sessions
WHERE expires_at < CURRENT_TIMESTAMP;
DELETE FROM sessions
WHERE expires_at < CURRENT_TIMESTAMP;DELETE FROM cart_items AS ci
USING products AS p
WHERE p.id = ci.product_id
AND p.discontinued = true;Note: Foreign-key actions, triggers, and permissions can affect or prevent a deletion. Review the schema before applying bulk changes.
Handle unique conflicts intentionally
PostgreSQL ON CONFLICT can ignore a conflicting insert or update the existing row. Identify a valid unique constraint or index as the conflict target, and use EXCLUDED to access the values proposed by the insert.
INSERT INTO tags (name)
VALUES ('sql')
ON CONFLICT (name) DO NOTHING;INSERT INTO counters (key, value)
VALUES ('views', 1)
ON CONFLICT (key) DO UPDATE
SET value = counters.value + EXCLUDED.value;Return values from modified rows
PostgreSQL RETURNING avoids a second query when the caller needs generated identifiers or changed values. Its expression list works like a SELECT output list and can be attached to INSERT, UPDATE, or DELETE.
INSERT INTO customers (name, email)
VALUES ('Ada', 'ada@example.com')
RETURNING id, name, email;UPDATE jobs
SET status = 'running'
WHERE status = 'queued'
RETURNING id, name, status;Group related changes in a transaction
A transaction makes a sequence atomic: COMMIT keeps it and ROLLBACK discards it. Savepoints allow a partial rollback without abandoning earlier work, but they do not replace application error handling or appropriate isolation choices.
BEGIN;
UPDATE accounts
SET balance = balance - 50
WHERE id = 1;
UPDATE accounts
SET balance = balance + 50
WHERE id = 2;
COMMIT;Note: Real financial transfers also need constraints, row locking, sufficient-funds checks, and an appropriate transaction isolation level.
BEGIN;
UPDATE orders SET status = 'processing' WHERE id = 42;
SAVEPOINT before_optional_step;
INSERT INTO notifications (order_id, channel) VALUES (42, 'email');
ROLLBACK TO SAVEPOINT before_optional_step;
COMMIT;BEGIN;
UPDATE products SET price = price * 2;
ROLLBACK;Sources and further reading
References
Authoritative documentation used to verify and expand this cheat sheet.
Help us improve
Found a typo or missing example?
Tell us what would make this cheat sheet clearer, more complete, or more useful.