CTE with INSERT, UPDATE, DELETE (writable CTEs) in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of UPDATE using writable CTE

Given the table employees with columns id, name, and salary, what will be the output of the following query?

WITH updated AS (
  UPDATE employees
  SET salary = salary * 1.1
  WHERE id = 2
  RETURNING id, salary
)
SELECT * FROM updated;

PostgreSQL

WITH updated AS (
  UPDATE employees
  SET salary = salary * 1.1
  WHERE id = 2
  RETURNING id, salary
)
SELECT * FROM updated;

A [{"id": 1, "salary": 50000}]

B []

C [{"id": 2, "salary": 55000}]

D [{"id": 2, "salary": 50000}]

💡 Hint

The CTE returns rows affected by the UPDATE with new salary values.

The UPDATE affects only the employee with id=2, increasing salary by 10%. The RETURNING clause outputs the updated row with new salary.

❓ query_result

intermediate

Result of DELETE with writable CTE

Consider the table products with columns product_id and stock. What will this query return?

WITH deleted AS (
  DELETE FROM products
  WHERE stock = 0
  RETURNING product_id
)
SELECT COUNT(*) FROM deleted;

PostgreSQL

WITH deleted AS (
  DELETE FROM products
  WHERE stock = 0
  RETURNING product_id
)
SELECT COUNT(*) FROM deleted;

A SyntaxError

B 0

C 1

D 3

💡 Hint

The CTE returns all deleted rows, then counts them.

The DELETE removes all products with stock=0 and RETURNING outputs those rows. COUNT(*) counts how many were deleted.

📝 Syntax

advanced

Identify syntax error in writable CTE with INSERT

Which option contains a syntax error in this writable CTE that inserts a new row?

WITH ins AS (
  INSERT INTO orders (order_id, amount)
  VALUES (101, 250)
  RETURNING order_id
)
SELECT * FROM ins;

PostgreSQL

WITH ins AS (
  INSERT INTO orders (order_id, amount)
  VALUES (101, 250)
  RETURNING order_id
)
SELECT * FROM ins;

A WITH ins AS (INSERT INTO orders order_id, amount VALUES (101, 250) RETURNING order_id) SELECT * FROM ins;

B WITH ins AS (INSERT INTO orders (order_id, amount) VALUES (101, 250) RETURNING order_id) SELECT * FROM ins;

C ;sni MORF * TCELES )di_redro GNINRUTER )052 ,101( SEULAV )tnuoma ,di_redro( sredro OTNI TRESNI( SA sni HTIW

D WITH ins AS (INSERT INTO orders (order_id, amount) VALUES (101, 250) RETURNING order_id) SELECT order_id FROM ins;

💡 Hint

Check the syntax of the INSERT statement inside the CTE.

Option A misses parentheses around columns in INSERT INTO clause, causing syntax error.

❓ optimization

advanced

Best practice for updating multiple rows with writable CTE

You want to increase salary by 5% for all employees in department 10 and get their updated salaries. Which query is most efficient and correct?

Options:
A) Use a writable CTE with UPDATE and RETURNING, then select from it.
B) Use a subquery to select employees, then update outside.
C) Use two separate queries: one UPDATE, one SELECT.
D) Use a writable CTE with DELETE and INSERT to replace rows.

A WITH updated AS (UPDATE employees SET salary = salary * 1.05 WHERE department_id = 10 RETURNING id, salary) SELECT * FROM updated;

B UPDATE employees SET salary = salary * 1.05 WHERE id IN (SELECT id FROM employees WHERE department_id = 10); SELECT id, salary FROM employees WHERE department_id = 10;

C UPDATE employees SET salary = salary * 1.05 WHERE department_id = 10; SELECT id, salary FROM employees WHERE department_id = 10;

D WITH replaced AS (DELETE FROM employees WHERE department_id = 10 RETURNING *) INSERT INTO employees SELECT * FROM replaced;

💡 Hint

Writable CTEs can update and return rows in one query.

Option A uses writable CTE to update and return updated rows efficiently in one step.

🧠 Conceptual

expert

Effect of writable CTE on transaction visibility

In PostgreSQL, when using a writable CTE with DELETE and RETURNING inside a transaction, what happens if you try to SELECT the deleted rows outside the CTE but before committing?

Choose the correct behavior:

A The deleted rows remain visible in all SELECTs until commit, including inside the CTE.

B The deleted rows are visible in the CTE but not visible in a separate SELECT until commit.

C The deleted rows are immediately removed from all SELECTs, including inside the CTE, even before commit.

D The DELETE inside the writable CTE does not affect visibility until the transaction commits.

💡 Hint

Think about how RETURNING works and transaction isolation in PostgreSQL.

Writable CTE's RETURNING shows deleted rows immediately inside the CTE, but outside SELECTs still see old data until commit.

Practice

1. What is the main purpose of using writable CTEs in PostgreSQL?

easy

A. To speed up SELECT queries by caching results

B. To create temporary tables that last beyond the current transaction

C. To perform INSERT, UPDATE, or DELETE operations inside a WITH clause

D. To define functions that return multiple rows

Solution

1. Step 1: Understand writable CTEs

   Writable CTEs allow data modification commands like INSERT, UPDATE, or DELETE inside a WITH clause.

2. Step 2: Compare options

   Options B, C, and D describe other features unrelated to writable CTEs.

3. Final Answer:

   To perform INSERT, UPDATE, or DELETE operations inside a WITH clause -> Option C

4. Quick Check:

   Writable CTEs = Data modification in WITH [OK]

Hint: Writable CTEs = data changes inside WITH clause [OK]

Common Mistakes:

• Confusing writable CTEs with temporary tables
• Thinking writable CTEs speed up SELECT queries
• Mixing writable CTEs with function definitions

2. Which of the following is the correct syntax to update rows using a writable CTE in PostgreSQL?

easy

A. UPDATE employees WITH (salary = salary * 1.1) RETURNING *;

B. WITH updated AS (UPDATE employees SET salary = salary * 1.1) RETURNING *;

C. WITH updated AS (SELECT * FROM employees WHERE salary > 5000) UPDATE updated SET salary = salary * 1.1;

D. WITH updated AS (UPDATE employees SET salary = salary * 1.1 RETURNING *) SELECT * FROM updated;

Solution

1. Step 1: Review writable CTE update syntax

   The correct syntax uses WITH updated AS (UPDATE ... RETURNING *) followed by a SELECT from updated.

2. Step 2: Analyze options

   WITH updated AS (UPDATE employees SET salary = salary * 1.1 RETURNING *) SELECT * FROM updated; matches the correct syntax. Options A, C, and D have syntax errors or misuse RETURNING.

3. Final Answer:

   WITH updated AS (UPDATE employees SET salary = salary * 1.1 RETURNING *) SELECT * FROM updated; -> Option D

4. Quick Check:

   Writable CTE update = WITH + UPDATE + RETURNING + SELECT [OK]

Hint: Use WITH (UPDATE ... RETURNING *) then SELECT [OK]

Common Mistakes:

• Omitting RETURNING clause in writable CTE
• Trying to UPDATE inside WITH without SELECT
• Misplacing RETURNING outside the CTE

3. Given the table products(id, name, stock) with rows: (1, 'Pen', 10), (2, 'Pencil', 5), what will be the result of this query?

WITH updated AS (UPDATE products SET stock = stock + 10 WHERE name = 'Pen' RETURNING *) SELECT * FROM updated;

medium

A. [{id:1, name:'Pen', stock:20}]

B. [{id:1, name:'Pen', stock:10}]

C. [{id:2, name:'Pencil', stock:15}]

D. Empty result set

Solution

1. Step 1: Identify updated rows

   The UPDATE increases stock by 10 only for product with name 'Pen' (id=1), changing stock from 10 to 20.

2. Step 2: Understand RETURNING and SELECT

   The RETURNING * returns the updated row, and the SELECT outputs it.

3. Final Answer:

   [{id:1, name:'Pen', stock:20}] -> Option A

4. Quick Check:

   Updated Pen stock = 10 + 10 = 20 [OK]

Hint: RETURNING shows updated rows immediately [OK]

Common Mistakes:

• Assuming all rows are updated
• Ignoring RETURNING clause output
• Confusing stock before and after update

4. Identify why this writable CTE query returns an empty result set even if matching rows exist:

WITH deleted AS (DELETE FROM orders WHERE status = 'cancelled') SELECT * FROM deleted;

medium

A. The WHERE clause syntax is incorrect

B. Missing RETURNING clause in DELETE statement

C. SELECT * FROM deleted should be SELECT COUNT(*)

D. DELETE cannot be used inside WITH clause

Solution

1. Step 1: Check DELETE in writable CTE

   DELETE inside WITH must include RETURNING to produce rows for the outer SELECT.

2. Step 2: Analyze the query

   The query lacks RETURNING, so 'deleted' has no rows to select from, resulting in an empty result set.

3. Final Answer:

   Missing RETURNING clause in DELETE statement -> Option B

4. Quick Check:

   Writable CTE DELETE needs RETURNING [OK]

Hint: Always add RETURNING with DELETE in writable CTEs [OK]

Common Mistakes:

• Omitting RETURNING after DELETE in CTE
• Thinking DELETE can't be in WITH clause
• Misunderstanding SELECT after writable CTE

5. You want to insert new users and immediately update their status to 'active' using writable CTEs. Which query correctly does this in one statement?

hard

A. WITH ins AS (INSERT INTO users(name) VALUES ('Alice'), ('Bob') RETURNING id, name) UPDATE users SET status = 'active' FROM ins WHERE users.id = ins.id RETURNING users.*;

B. INSERT INTO users(name) VALUES ('Alice'), ('Bob'); WITH upd AS (UPDATE users SET status = 'active' WHERE name IN ('Alice', 'Bob') RETURNING *) SELECT * FROM upd;

C. WITH ins AS (INSERT INTO users(name) VALUES ('Alice'), ('Bob')) UPDATE users SET status = 'active' WHERE id IN (SELECT id FROM ins);

D. WITH ins AS (INSERT INTO users(name) VALUES ('Alice'), ('Bob')) UPDATE ins SET status = 'active' WHERE name IN ('Alice', 'Bob');

Solution

1. Step 1: Insert and capture new rows

   Use a writable CTE to insert and RETURNING id, name to identify new users.

2. Step 2: Update using CTE results

   Update users joining with inserted rows on id, setting status to 'active', and RETURNING updated rows.

3. Step 3: Analyze options

   WITH ins AS (INSERT INTO users(name) VALUES ('Alice'), ('Bob') RETURNING id, name) UPDATE users SET status = 'active' FROM ins WHERE users.id = ins.id RETURNING users.*; correctly chains insert and update in one statement with proper RETURNING and join. INSERT INTO users(name) VALUES ('Alice'), ('Bob'); WITH upd AS (UPDATE users SET status = 'active' WHERE name IN ('Alice', 'Bob') RETURNING *) SELECT * FROM upd; separates insert and update into multiple statements. WITH ins AS (INSERT INTO users(name) VALUES ('Alice'), ('Bob')) UPDATE users SET status = 'active' WHERE id IN (SELECT id FROM ins); lacks RETURNING so CTE returns no rows and subquery updates nothing. WITH ins AS (INSERT INTO users(name) VALUES ('Alice'), ('Bob')) UPDATE ins SET status = 'active' WHERE name IN ('Alice', 'Bob'); incorrectly attempts to UPDATE the CTE which is invalid.

4. Final Answer:

   WITH ins AS (INSERT INTO users(name) VALUES ('Alice'), ('Bob') RETURNING id, name) UPDATE users SET status = 'active' FROM ins WHERE users.id = ins.id RETURNING users.*; -> Option A

5. Quick Check:

   Writable CTE insert + update with join = WITH ins AS (INSERT INTO users(name) VALUES ('Alice'), ('Bob') RETURNING id, name) UPDATE users SET status = 'active' FROM ins WHERE users.id = ins.id RETURNING users.*; [OK]

Hint: Chain INSERT and UPDATE with RETURNING and FROM join [OK]

Common Mistakes:

• Omitting RETURNING clause before using CTE in subquery
• Splitting insert and update into separate statements
• Omitting RETURNING to get updated rows