CTE with INSERT, UPDATE, DELETE (writable CTEs) in PostgreSQL - Deep Dive

Overview - CTE with INSERT, UPDATE, DELETE (writable CTEs)

What is it?

Writable Common Table Expressions (CTEs) in PostgreSQL allow you to perform INSERT, UPDATE, or DELETE operations inside a WITH clause. This means you can write queries that modify data and then use the results immediately in the same query. It helps organize complex data changes in a clear, step-by-step way.

Why it matters

Without writable CTEs, you would need multiple separate queries to change data and then fetch results, which can be inefficient and harder to read. Writable CTEs let you combine these steps, making your database operations faster and your code easier to understand and maintain.

Where it fits

Before learning writable CTEs, you should understand basic SQL SELECT queries, and simple INSERT, UPDATE, and DELETE commands. After this, you can explore advanced query optimization, transaction control, and recursive CTEs for more complex data manipulations.

Mental Model

Core Idea

Writable CTEs let you perform data changes and immediately use those changed rows in the same query, like writing and reading from a temporary notebook in one go.

Think of it like...

Imagine you have a whiteboard where you first erase some notes, then write new ones, and finally read the updated notes all without leaving the board. Writable CTEs let you do this in your database query.

WITH writable_cte AS (
  ┌───────────────┐
  │  INSERT/UPDATE/│
  │  DELETE query │
  └──────┬────────┘
         │
         ▼
  ┌───────────────┐
  │  Modified rows │
  └───────────────┘
SELECT * FROM writable_cte;

Build-Up - 8 Steps

1

Foundation

Understanding Basic CTEs

Concept: Learn what a Common Table Expression (CTE) is and how it structures queries.

A CTE is a temporary named result set you define using WITH before your main query. It helps break down complex queries into simpler parts. For example: WITH temp AS (SELECT * FROM employees WHERE department = 'Sales') SELECT * FROM temp WHERE salary > 50000; This runs the inner query first, then uses its result in the outer query.

Result

You get all sales employees earning more than 50,000.

Understanding CTEs is key because writable CTEs build on this idea by adding data changes inside the WITH clause.

2

Foundation

Reviewing Basic INSERT, UPDATE, DELETE

Concept: Recall how to add, change, or remove rows in a table using simple SQL commands.

INSERT adds new rows: INSERT INTO employees (name, department) VALUES ('Alice', 'HR'); UPDATE changes existing rows: UPDATE employees SET department = 'Marketing' WHERE name = 'Alice'; DELETE removes rows: DELETE FROM employees WHERE name = 'Alice';

Result

Data in the employees table is added, changed, or removed accordingly.

Knowing these commands is essential because writable CTEs combine them with CTE syntax for more powerful queries.

3

Intermediate

Writable CTE Syntax Basics

🤔Before reading on: do you think writable CTEs can return the modified rows immediately? Commit to yes or no.

Concept: Writable CTEs let you run INSERT, UPDATE, or DELETE inside a WITH clause and return the affected rows for further use.

Example of an UPDATE writable CTE: WITH updated AS ( UPDATE employees SET salary = salary * 1.1 WHERE department = 'Sales' RETURNING * ) SELECT * FROM updated; This updates salaries and returns the updated rows in one query.

Result

You see the rows with updated salaries right after the update runs.

Writable CTEs let you chain data changes and immediately see or use the results, improving query clarity and efficiency.

4

Intermediate

Using INSERT in Writable CTEs

🤔Before reading on: do you think you can insert rows and then select them in the same query? Commit to yes or no.

Concept: You can insert new rows inside a writable CTE and then select those rows immediately after.

Example: WITH new_emps AS ( INSERT INTO employees (name, department) VALUES ('Bob', 'IT'), ('Carol', 'IT') RETURNING * ) SELECT * FROM new_emps; This inserts two new employees and returns their full rows.

Result

The query outputs the newly inserted employee rows.

This pattern helps add data and use it right away without separate queries, useful for workflows needing immediate feedback.

5

Intermediate

DELETE with Writable CTEs

🤔Before reading on: do you think DELETE in a writable CTE can return the deleted rows? Commit to yes or no.

Concept: DELETE commands inside writable CTEs can remove rows and return those deleted rows for further processing.

Example: WITH removed AS ( DELETE FROM employees WHERE department = 'Temp' RETURNING * ) SELECT * FROM removed; This deletes all 'Temp' department employees and returns their details.

Result

You get a list of all deleted employees from the 'Temp' department.

Returning deleted rows helps audit or chain deletions with other operations in one query.

6

Advanced

Chaining Multiple Writable CTEs

🤔Before reading on: do you think you can perform multiple data changes in sequence using writable CTEs? Commit to yes or no.

Concept: You can chain several writable CTEs to perform multiple INSERT, UPDATE, or DELETE steps in one query.

Example: WITH inserted AS ( INSERT INTO employees (name, department) VALUES ('Dave', 'HR') RETURNING * ), updated AS ( UPDATE employees SET salary = salary + 1000 WHERE department = 'HR' RETURNING * ) SELECT * FROM updated; This inserts a new employee, then updates salaries in HR, returning updated rows.

Result

The final output shows all HR employees with updated salaries after the insert.

Chaining writable CTEs lets you build complex data workflows in a single, readable query.

7

Advanced

Writable CTEs and Transaction Control

🤔Before reading on: do you think writable CTEs run inside a single transaction automatically? Commit to yes or no.

Concept: Writable CTEs run all their data changes inside one transaction, so either all changes succeed or none do.

If any writable CTE step fails, the entire query rolls back. For example, if an INSERT violates a constraint, no changes apply. This ensures data consistency without manual transaction commands.

Result

Either all changes from writable CTEs apply together, or none apply if an error occurs.

Knowing writable CTEs are atomic helps prevent partial updates and keeps data reliable.

8

Expert

Performance and Planning with Writable CTEs

🤔Before reading on: do you think writable CTEs always optimize away like simple SELECT CTEs? Commit to yes or no.

Concept: Writable CTEs are optimization fences; PostgreSQL executes them fully before continuing, which can affect performance and planning.

Unlike read-only CTEs that can be inlined or optimized away, writable CTEs force execution order and materialize results. This guarantees correctness but may slow queries if overused or chained unnecessarily.

Result

Writable CTEs ensure data changes happen in order but may reduce query planner flexibility.

Understanding writable CTEs as optimization barriers helps experts write efficient queries and avoid unexpected slowdowns.

Under the Hood

Writable CTEs execute their data modification statement (INSERT, UPDATE, DELETE) first and store the affected rows temporarily. Then, the outer query can select from this temporary result. PostgreSQL treats writable CTEs as separate execution steps, ensuring all changes are done before moving on. This behavior guarantees atomicity and visibility of changes within the same query.

Why designed this way?

Writable CTEs were designed to combine data modification and retrieval in one statement for clarity and atomicity. The execution fence prevents partial or out-of-order changes, avoiding data inconsistencies. Alternatives like separate queries or procedural code were less elegant and harder to maintain.

┌─────────────────────────────┐
│ WITH writable_cte AS (      │
│   INSERT/UPDATE/DELETE       │
│   RETURNING *               │
│ )                          │
└─────────────┬───────────────┘
              │ Executes data change
              ▼
┌─────────────────────────────┐
│ Temporary result set stored  │
│ (affected rows)              │
└─────────────┬───────────────┘
              │ Used by outer query
              ▼
┌─────────────────────────────┐
│ SELECT * FROM writable_cte; │
└─────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do writable CTEs always improve performance? Commit to yes or no.

Common Belief: Writable CTEs always make queries faster because they combine steps.

Reality: Writable CTEs can slow queries because they act as optimization fences, forcing PostgreSQL to execute them fully before continuing.

Why it matters: Assuming writable CTEs always improve speed can lead to inefficient queries and unexpected slowdowns in production.

Quick: Can writable CTEs be used to update multiple tables at once? Commit to yes or no.

Common Belief: Writable CTEs let you update multiple tables in one statement easily.

Reality: Each writable CTE modifies only one table; to change multiple tables, you chain multiple writable CTEs, one per table.

Why it matters: Expecting multi-table updates in one writable CTE can cause confusion and errors in query design.

Quick: Does RETURNING clause in writable CTEs return old values by default? Commit to yes or no.

Common Belief: RETURNING returns the original rows before modification.

Reality: RETURNING returns the new state of rows after INSERT or UPDATE, or the deleted rows for DELETE.

Why it matters: Misunderstanding RETURNING can cause bugs when relying on outdated data in subsequent query parts.

Quick: Can writable CTEs be used inside transactions with partial commits? Commit to yes or no.

Common Belief: Writable CTEs allow partial commits inside a transaction.

Reality: Writable CTEs run atomically; either all changes succeed or all fail together within the transaction.

Why it matters: Expecting partial commits can lead to data inconsistency and unexpected rollback behavior.

Expert Zone

1

Writable CTEs act as optimization fences, so overusing them can prevent query planner optimizations and slow down complex queries.

2

The RETURNING clause in writable CTEs can be used to chain data modifications and feed results into subsequent CTEs, enabling complex multi-step workflows.

3

Writable CTEs always run inside the current transaction context, so their changes are visible only after the transaction commits, ensuring data consistency.

When NOT to use

Avoid writable CTEs when you need maximum query planner optimization or when simple separate statements suffice. For bulk data changes, consider using batch scripts or procedural languages like PL/pgSQL. Also, avoid writable CTEs if you need partial commits within a transaction; use explicit transaction control instead.

Production Patterns

Writable CTEs are used in production to perform audit logging by capturing changed rows, to implement complex business logic in a single query, and to maintain data integrity by chaining dependent data modifications atomically.

Connections

Transactions

Writable CTEs run inside transactions and rely on transaction atomicity.

Understanding transactions helps grasp why writable CTEs either fully succeed or fail, ensuring data consistency.

Functional Programming

Writable CTEs resemble chaining pure functions that transform data step-by-step.

Seeing writable CTEs as data transformation pipelines clarifies how complex changes are composed cleanly.

Version Control Systems

Writable CTEs' RETURNING clause is like staging changes before committing in version control.

This connection highlights how writable CTEs let you preview and use changes before finalizing them.

Common Pitfalls

#1 Trying to update multiple tables in one writable CTE.

Wrong approach:

WITH update_all AS (
UPDATE employees SET salary = salary + 1000 RETURNING *,
UPDATE departments SET budget = budget - 1000 RETURNING *
)
SELECT * FROM update_all;

Correct approach:

WITH update_emps AS (
UPDATE employees SET salary = salary + 1000 RETURNING *
),
update_depts AS (
UPDATE departments SET budget = budget - 1000 RETURNING *
)
SELECT * FROM update_emps;

Root cause:

Misunderstanding that each writable CTE can only modify one table; multiple tables require multiple CTEs.

#2 Omitting RETURNING clause in writable CTEs expecting results.

Wrong approach:

WITH updated AS (
UPDATE employees SET salary = salary * 1.1 WHERE department = 'Sales'
)
SELECT * FROM updated;

Correct approach:

WITH updated AS (
UPDATE employees SET salary = salary * 1.1 WHERE department = 'Sales' RETURNING *
)
SELECT * FROM updated;

Root cause:

Not realizing RETURNING is needed to output modified rows from writable CTEs.

#3 Assuming writable CTEs improve performance in all cases.

Wrong approach:

Using many chained writable CTEs for minor updates without testing performance.

Correct approach:

Benchmark queries and use writable CTEs judiciously, avoiding unnecessary chains that block optimization.

Root cause:

Believing combining queries always speeds them up without considering optimization fences.

Key Takeaways

Writable CTEs let you perform INSERT, UPDATE, or DELETE inside a WITH clause and immediately use the affected rows.

They run atomically inside a transaction, ensuring all changes succeed or fail together.

Writable CTEs act as optimization fences, so overusing them can reduce query performance.

Each writable CTE modifies only one table; to change multiple tables, chain multiple writable CTEs.

The RETURNING clause is essential to get the modified rows from writable CTEs for further use.