Modifying Data & Transactions

Reading data is forgiving; writing it is not. This page covers the three write statements, the upsert patterns both engines support, and the transaction semantics that decide what happens when things go wrong.

INSERT

Single row

INSERT INTO authors (name, birth_year) VALUES ('N. K. Jemisin', 1972);

Always name the columns. INSERT INTO authors VALUES (...) depends on column order and breaks the moment someone runs ALTER TABLE ... ADD COLUMN.

Omitted columns take their DEFAULT, or NULL if there isn’t one. A NOT NULL column with no default and no supplied value is an error.

Multiple rows

One statement, many rows — far faster than N statements because it is one parse, one plan, one round trip.

INSERT INTO authors (name, birth_year) VALUES
  ('N. K. Jemisin',   1972),
  ('Ann Leckie',      1966),
  ('Becky Chambers',  1985);

Both engines support this. For bulk loads of thousands of rows, use the engine’s loader instead:

# PostgreSQL: server-side COPY, or \copy from the client
psql -d bookstore -c "\copy books(title, year, price) FROM 'books.csv' CSV HEADER"

# SQLite
sqlite3 bookstore.db ".mode csv" ".import --skip 1 books.csv books"

INSERT … SELECT

Insert the result of a query. The select list must line up positionally with the column list.

INSERT INTO archived_books (id, title, year)
SELECT id, title, year FROM books WHERE year < 1970;

This is also how you copy between tables, deduplicate into a new table, or backfill a column.

Other forms

INSERT INTO events DEFAULT VALUES;        -- every column takes its default; both engines

UPDATE

UPDATE books SET price = price * 1.10 WHERE year < 1980;
UPDATE books SET price = 12.00, in_print = false WHERE id = 3;

The right-hand side sees the old values of the row, so SET a = b, b = a swaps them correctly in both engines.

Danger

UPDATE without WHERE updates every row. There is no undo outside a transaction.

Habit that saves you: run the WHERE as a SELECT first.

SELECT COUNT(*) FROM books WHERE year < 1980;   -- does this number look right?
UPDATE books SET price = price * 1.10 WHERE year < 1980;

Or wrap it: BEGIN; UPDATE ...; -- check, then COMMIT or ROLLBACK.

UPDATE from another table

To set values based on a join, both engines support UPDATE ... FROM — PostgreSQL always, SQLite since 3.33.0.

UPDATE books
SET price = p.new_price
FROM pending_prices AS p
WHERE p.book_id = books.id;

Note the shape: the target table is not repeated in FROM, and the join condition lives in WHERE.

The fully portable alternative uses a correlated subquery:

UPDATE books
SET price = (SELECT new_price FROM pending_prices p WHERE p.book_id = books.id)
WHERE EXISTS (SELECT 1 FROM pending_prices p WHERE p.book_id = books.id);

The WHERE EXISTS is essential. Without it, books with no pending price get their price set to NULL.

Caution

In PostgreSQL, if FROM matches multiple rows for the same target row, the target is updated once using an arbitrarily chosen matching row. Make sure your join is unique on the target side.

DELETE

DELETE FROM books WHERE year < 1900;
DELETE FROM books;                     -- every row

Deleting via a related table:

-- Portable
DELETE FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE city IS NULL);

-- PostgreSQL's join form
DELETE FROM orders AS o USING customers AS c
WHERE c.id = o.customer_id AND c.city IS NULL;

PostgreSQL also has TRUNCATE, which empties a table far faster than DELETE and reclaims disk space immediately:

TRUNCATE TABLE orders;
TRUNCATE orders, order_items RESTART IDENTITY CASCADE;

SQLite has no TRUNCATE; DELETE FROM t with no WHERE triggers an internal truncate optimisation that does the same job.

Note

Deletes fail if a foreign key points at the row and the constraint says NO ACTION or RESTRICT. Use ON DELETE CASCADE on the child table if cascading removal is what you want — and remember SQLite ignores foreign keys unless PRAGMA foreign_keys = ON.

Upsert: INSERT … ON CONFLICT

“Insert, or update if it already exists.” Both engines implement the same ON CONFLICT syntax — PostgreSQL 9.5+ and SQLite 3.24+.

CREATE TABLE page_views (
  path  TEXT PRIMARY KEY,
  views INTEGER NOT NULL DEFAULT 0,
  last_seen TEXT
);

DO NOTHING

Skip the row silently if it conflicts:

INSERT INTO page_views (path, views) VALUES ('/about', 1)
ON CONFLICT DO NOTHING;

INSERT INTO page_views (path, views) VALUES ('/about', 1)
ON CONFLICT (path) DO NOTHING;      -- only this constraint counts as a conflict

Without a conflict target, any unique-constraint violation is swallowed — including ones you didn’t anticipate. Naming the target is safer.

DO UPDATE

The real upsert. The pseudo-table excluded holds the row you tried to insert.

INSERT INTO page_views (path, views, last_seen)
VALUES ('/about', 1, '2026-08-09')
ON CONFLICT (path) DO UPDATE
SET views = page_views.views + excluded.views,
    last_seen = excluded.last_seen;

Run it twice and views becomes 2. Rules that apply to both engines:

• DO UPDATE requires a conflict target — a column list, or an expression matching a unique index.
• The target must correspond to a real unique constraint or unique index. ON CONFLICT (title) on a non-unique column is an error.
• Qualify ambiguous names: page_views.views is the existing row, excluded.views is the proposed row.
• You can add a WHERE to the update to make it conditional:

ON CONFLICT (path) DO UPDATE SET views = excluded.views
WHERE page_views.views < excluded.views;      -- only move the counter forward

• A WHERE after the conflict target narrows which index entries arbitrate — used with partial indexes:

INSERT INTO page_views (path, views) VALUES ('/about', 1)
ON CONFLICT (path) WHERE views > 0 DO UPDATE SET views = excluded.views;

PostgreSQL additionally allows targeting a named constraint:

-- PostgreSQL only
ON CONFLICT ON CONSTRAINT page_views_pkey DO UPDATE SET views = excluded.views;

SQLite’s OR-clauses

SQLite has an older, blunter mechanism on INSERT and UPDATE:

INSERT OR IGNORE  INTO page_views (path, views) VALUES ('/about', 1);
INSERT OR REPLACE INTO page_views (path, views) VALUES ('/about', 1);
REPLACE INTO      page_views (path, views) VALUES ('/about', 1);  -- same thing

Danger

INSERT OR REPLACE deletes the existing row and inserts a new one. Columns you didn’t mention revert to their defaults, ON DELETE CASCADE triggers fire and can wipe child rows, and the rowid changes. It is almost never what you meant.

Use ON CONFLICT ... DO UPDATE instead. It updates in place and is portable to PostgreSQL.

INSERT OR IGNORE is equivalent to ON CONFLICT DO NOTHING and is harmless, just non-portable.

MERGE (PostgreSQL 15+)

PostgreSQL 15 added the SQL-standard MERGE, which can insert, update, and delete in one pass:

-- PostgreSQL 15+
MERGE INTO page_views AS t
USING incoming AS s ON t.path = s.path
WHEN MATCHED THEN UPDATE SET views = t.views + s.views
WHEN NOT MATCHED THEN INSERT (path, views) VALUES (s.path, s.views);

SQLite has no MERGE. For plain upserts, ON CONFLICT is simpler and works in both.

RETURNING

RETURNING makes a write statement return rows, so you get generated IDs, computed defaults, and old-vs-new values without a second query. PostgreSQL has always had it; SQLite added it in 3.35.0 (March 2021).

INSERT INTO authors (name, birth_year) VALUES ('Ann Leckie', 1966)
RETURNING id, name;

UPDATE books SET price = price * 1.1 WHERE year < 1980
RETURNING id, title, price;

DELETE FROM orders WHERE qty = 0
RETURNING *;

 id |    name
----+------------
  4 | Ann Leckie

This is the correct way to get an auto-generated primary key — it is atomic and works with multi-row inserts, unlike last_insert_rowid() or currval().

Caution

Differences to know:

• Neither engine accepts ORDER BY or LIMIT on a RETURNING clause, and neither guarantees the order of returned rows. Sort in the application, or wrap the statement in a CTE (PostgreSQL) and sort the outer query.
• PostgreSQL’s RETURNING on UPDATE gives the new values. To see the old ones too, capture the pre-image in a CTE.
• SQLite emits RETURNING rows as they are computed, before the statement finishes. Do not run further statements against the same table while iterating the results.

Transactions and ACID

A transaction groups statements so they succeed or fail as a unit. ACID names the four guarantees:

• Atomicity — all statements commit, or none do. A crash mid-transaction leaves no partial work.
• Consistency — constraints hold at commit; the database never moves to an invalid state.
• Isolation — concurrent transactions don’t see each other’s uncommitted work.
• Durability — once COMMIT returns, the data survives a power loss.

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

If the second UPDATE fails, ROLLBACK undoes the first. Without a transaction, money vanishes.

ROLLBACK;   -- discard everything since BEGIN

Both engines accept BEGIN, BEGIN TRANSACTION, COMMIT, END (as a synonym for COMMIT), and ROLLBACK.

Autocommit

Outside an explicit transaction, every statement is its own transaction in both engines. That is why a single UPDATE affecting a million rows is still all-or-nothing.

The practical consequence: inserting 10,000 rows one statement at a time means 10,000 commits, each with an fsync. Wrapping them in one transaction can be 100× faster.

BEGIN;
INSERT INTO books (...) VALUES (...);   -- ×10000
COMMIT;

Caution

Database drivers differ on autocommit. Python’s sqlite3 module historically opened implicit transactions before DML and required an explicit commit(); psycopg opens a transaction on the first statement and needs commit() too. Node’s better-sqlite3 is genuinely autocommit. Check your driver — assuming the wrong model is a common source of “my writes disappeared”.

PostgreSQL: MVCC and isolation levels

PostgreSQL uses MVCC: each transaction sees a snapshot of the database. Writers never block readers and readers never block writers, because an updated row is written as a new version while the old version stays visible to transactions that started earlier.

Set the isolation level per transaction:

BEGIN ISOLATION LEVEL REPEATABLE READ;
-- ...
COMMIT;

-- or
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Level	What you get
Read Committed (default)	Each statement sees a fresh snapshot. Two identical SELECTs in one transaction can return different data.
Repeatable Read	The whole transaction sees one snapshot taken at its first statement. No non-repeatable reads, no phantoms.
Serializable	As if transactions ran one at a time. Detects write skew and other anomalies.

PostgreSQL accepts READ UNCOMMITTED for compatibility but treats it as READ COMMITTED — it never shows uncommitted data.

Caution

REPEATABLE READ and SERIALIZABLE can abort a transaction at commit time with a serialization failure (SQLSTATE 40001). This is not a bug; it is the mechanism. Your application must catch it and retry the whole transaction from the beginning. Code that uses these levels without a retry loop is broken.

Explicit locking

When you need to read a row and then update it based on what you read, take a lock:

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;   -- blocks other writers
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

FOR UPDATE variants: FOR NO KEY UPDATE (weaker), FOR SHARE, FOR KEY SHARE. Adding SKIP LOCKED gives you a work-queue pattern:

SELECT id FROM jobs WHERE status = 'pending'
ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;

SQLite supports none of these — it doesn’t need row locks because it doesn’t have concurrent writers.

The aborted-transaction rule

Danger

In PostgreSQL, any error inside a transaction aborts the whole transaction. Every subsequent statement fails with current transaction is aborted, commands ignored until end of transaction block until you ROLLBACK (or roll back to a savepoint).

SQLite does not do this. Most errors leave the transaction usable and you can carry on. Code written against SQLite that “handles” a failed insert and continues will break on PostgreSQL — use a savepoint there.

SQLite: the locking model

SQLite has no MVCC in the PostgreSQL sense and no row locks. Concurrency is managed at the database file level.

Three ways to start a transaction:

BEGIN;            -- same as BEGIN DEFERRED
BEGIN DEFERRED;   -- take no lock now; acquire on first read/write
BEGIN IMMEDIATE;  -- take the write lock now
BEGIN EXCLUSIVE;  -- take the write lock and block other readers (rollback-journal mode)

A deferred transaction that reads first and writes later must upgrade its lock. If another connection wrote in the meantime, the upgrade fails with SQLITE_BUSY — and the error cannot be resolved by waiting, because your read snapshot is already stale. You must roll back and retry.

Tip

If a transaction is going to write at all, start it with BEGIN IMMEDIATE. This takes the write lock up front, so you either wait once at the start or fail fast — you never hit the unrecoverable mid-transaction upgrade failure. This one habit eliminates most SQLITE_BUSY pain.

WAL mode

The default rollback journal blocks readers during a write. Write-Ahead Logging removes that:

PRAGMA journal_mode = WAL;

In WAL mode, readers see a consistent snapshot and are never blocked by the writer; the writer is never blocked by readers. There is still exactly one writer at a time for the whole database — that limit is architectural.

WAL is persistent (set once per database file, not per connection) and creates two sidecar files, -wal and -shm, alongside your .db. Copying only the .db file while the WAL is non-empty gives you an incomplete backup — use .backup or VACUUM INTO instead.

Pair it with a busy timeout so contending connections wait instead of erroring instantly:

PRAGMA busy_timeout = 5000;   -- milliseconds

Common production preamble for a SQLite connection:

PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
PRAGMA foreign_keys = ON;
PRAGMA synchronous = NORMAL;   -- safe with WAL; much faster than FULL

Note

synchronous = NORMAL in WAL mode can lose the most recent transactions on a power failure but never corrupts the database. FULL (the default) is durable against power loss. Choose deliberately.

Savepoints

A savepoint is a named point inside a transaction you can roll back to without discarding everything. Both engines support them with identical syntax.

BEGIN;
INSERT INTO authors (name) VALUES ('Author A');

SAVEPOINT sp1;
INSERT INTO authors (name) VALUES ('Author B');
ROLLBACK TO SAVEPOINT sp1;      -- Author B is gone, Author A survives, txn continues

RELEASE SAVEPOINT sp1;          -- discard the savepoint, keep the work
COMMIT;

ROLLBACK TO does not end the transaction — it rewinds it. RELEASE forgets the savepoint and merges its work into the enclosing transaction.

Two practical uses:

Partial failure handling in PostgreSQL. Since an error aborts the transaction, wrap the risky statement:

BEGIN;
INSERT INTO log (msg) VALUES ('starting');
SAVEPOINT maybe;
INSERT INTO books (id, title) VALUES (1, 'duplicate');   -- might violate the PK
-- on error:
ROLLBACK TO maybe;
-- carry on
INSERT INTO log (msg) VALUES ('done');
COMMIT;

Nested logic. SQLite’s SAVEPOINT name outside any transaction starts one, so library code can use savepoints without knowing whether a transaction is already open.

Key points

• Always list columns in INSERT; batch rows into one statement, or use COPY / .import for bulk loads.
• UPDATE/DELETE without WHERE hit every row — check with a SELECT first, or work inside a transaction.
• ON CONFLICT ... DO UPDATE with the excluded pseudo-table is the portable upsert (PostgreSQL 9.5+, SQLite 3.24+).
• Avoid SQLite’s INSERT OR REPLACE — it deletes and reinserts, losing columns and firing cascades.
• RETURNING gives you generated keys atomically; PostgreSQL always, SQLite 3.35+.
• Every statement is its own transaction by default; batching writes into one transaction is a large speedup.
• PostgreSQL: MVCC, three usable isolation levels, and any error aborts the transaction — retry serialization failures, use savepoints for partial failure.
• SQLite: one writer at a time; use BEGIN IMMEDIATE for write transactions, and enable WAL plus a busy timeout.