Postgres batch UPDATE via `WITH ... VALUES` CTE

README.md

Postgres batch UPDATE via WITH ... VALUES CTE

When updating many rows, sending individual UPDATE ... WHERE id = ... statements is slow and chatty. A practical alternative is a single UPDATE ... FROM joined with a WITH updated_data AS (VALUES ...) CTE.

This pattern:

• sends one statement per batch
• keeps the update logic in SQL
• works great with psycopg / prepared parameters
• avoids temporary tables for many use cases

---

Core pattern

WITH updated_data (id, new_status, new_value) AS (
  VALUES
    ($1, $2, $3),
    ($4, $5, $6),
    ...
)
UPDATE target t
SET
  status = u.new_status,
  some_value = u.new_value
FROM updated_data u
WHERE t.id = u.id;