Data-Modifying CTEs, RETURNING and MERGE

RETURNING — A Quick Recap

Every INSERT, UPDATE, and DELETE can hand back the affected rows in the same statement — no follow-up SELECT needed (covered individually in earlier lessons):

DELETE FROM employees WHERE department = 'Support' RETURNING id, name;

Data-Modifying CTEs — Chaining Writes Together

A CTE (see the CTEs lesson) doesn't have to just SELECT — it can wrap an INSERT/UPDATE/DELETE, and later parts of the query can read its RETURNING output.

WITH moved AS (
  DELETE FROM employees
  WHERE department = 'Support'
  RETURNING *
)
INSERT INTO archived_employees
SELECT * FROM moved;
-- deletes rows from employees AND inserts the same rows into archived_employees, atomically

WITH raised AS (
  UPDATE employees
  SET salary = salary * 1.1
  WHERE department = 'Engineering'
  RETURNING id, salary
)
SELECT COUNT(*), SUM(salary) FROM raised;
-- immediately summarize the rows you just updated

This all happens as one atomic operation — either the whole chain succeeds, or none of it does.

MERGE — Combined Insert/Update/Delete Logic

MERGE (added in PostgreSQL 15) lets you specify different actions depending on whether a matching row was found — more flexible than INSERT ... ON CONFLICT (see the Upsert lesson), which only handles insert-or-update.

MERGE INTO employees AS target
USING staging_employees AS source
ON target.email = source.email
WHEN MATCHED AND source.is_terminated THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET salary = source.salary, department = source.department
WHEN NOT MATCHED THEN
  INSERT (name, email, salary, department)
  VALUES (source.name, source.email, source.salary, source.department);

This single statement syncs employees against a staging_employees table: updates matched rows, deletes terminated employees, and inserts anyone new — the classic pattern for loading daily HR data exports.

MERGE vs ON CONFLICT

	INSERT ... ON CONFLICT	MERGE
Source	A VALUES list or query	Any table/query, matched by a join condition
Actions	Insert, or one update/nothing on conflict	Different actions per branch: insert, update, AND delete
Best for	Simple upserts	Syncing a whole table against another (e.g. staging → production)

Common Errors & Troubleshooting

• ERROR: MERGE is not supported in this PostgreSQL version — MERGE was introduced in PostgreSQL 15; check SELECT version(); and use INSERT ... ON CONFLICT instead on older versions.
• A data-modifying CTE running its write more than once — each CTE in a WITH clause executes exactly once, but referencing it multiple times in the main query does NOT re-run the write — if results look wrong, check the surrounding query logic instead, since the CTE itself only writes once.
• MERGE matching the wrong rows — the ON condition in MERGE behaves like a join condition; a too-loose match (e.g. matching on a non-unique column) can cause unexpected rows to be updated or deleted — make sure the match column is unique on at least one side.

Key Takeaways

• A data-modifying CTE lets you chain a DELETE/UPDATE/INSERT and immediately consume its RETURNING output — all atomically, in one statement.
• MERGE handles insert, update, and delete branches based on a match condition — ON CONFLICT only covers insert-or-update.
• Both are especially useful for data pipeline patterns like archiving old rows or syncing a staging table into production.