Why CTE with INSERT, UPDATE, DELETE (writable CTEs) in PostgreSQL? - Purpose & Use Cases

The Big Idea

What if you could update, add, and delete data all at once without mistakes?

The Scenario

Imagine you have a big spreadsheet where you need to update some rows, add new ones, and remove others all at once. Doing this by hand means flipping back and forth between sheets, copying data, and hoping you don't make mistakes.

The Problem

Manually changing data like this is slow and risky. You might forget to update a related row or accidentally delete the wrong entry. It's hard to keep track of all changes and ensure everything stays consistent.

The Solution

Writable CTEs let you group these changes together in one clear, organized command. You can insert, update, and delete rows step-by-step inside a single query, making your work faster and safer.

Before vs After

✗ Before

UPDATE users SET active = false WHERE last_login < '2023-01-01';
INSERT INTO users (name) VALUES ('New User');
DELETE FROM users WHERE banned = true;

✓ After

WITH updated AS (
  UPDATE users SET active = false WHERE last_login < '2023-01-01' RETURNING *
), inserted AS (
  INSERT INTO users (name) VALUES ('New User') RETURNING *
), deleted AS (
  DELETE FROM users WHERE banned = true RETURNING *
)
SELECT * FROM updated UNION ALL SELECT * FROM inserted UNION ALL SELECT * FROM deleted;

What It Enables

This lets you perform multiple data changes in one smooth, reliable step, keeping your database clean and consistent.

Real Life Example

A company cleaning up its customer database can deactivate old accounts, add new sign-ups, and remove banned users all in one go without juggling multiple commands.

Key Takeaways

Manual data changes are slow and error-prone.

Writable CTEs group insert, update, and delete in one query.

This makes database updates safer, clearer, and faster.