CTE with INSERT, UPDATE, DELETE (writable CTEs) in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a writable CTE in PostgreSQL?

A writable CTE is a Common Table Expression that allows you to perform INSERT, UPDATE, or DELETE operations inside the CTE and then use the affected rows in the main query.

beginner

How do you use an UPDATE inside a writable CTE?

You write a CTE with the UPDATE statement, then select from it to see the updated rows or use them in further queries.

intermediate

Can you perform multiple writable CTEs in a single query?

Yes, you can chain multiple writable CTEs (INSERT, UPDATE, DELETE) in one query, and each can use the results of the previous ones.

intermediate

What is the benefit of using writable CTEs instead of separate statements?

Writable CTEs let you combine data modification and retrieval in one query, making your code cleaner and sometimes more efficient.

beginner

Give an example of a DELETE operation inside a writable CTE.

WITH deleted_rows AS (DELETE FROM table_name WHERE condition RETURNING *) SELECT * FROM deleted_rows;

What does a writable CTE allow you to do in PostgreSQL?

A Create temporary tables

B Only SELECT data inside a CTE

C Perform INSERT, UPDATE, or DELETE inside a CTE

D Run stored procedures

Writable CTEs allow data modification operations like INSERT, UPDATE, and DELETE inside the CTE.

Which keyword is used to get the affected rows from a writable CTE operation?

A INTO

B RETURNING

C FETCH

D OUTPUT

The RETURNING clause returns the rows affected by INSERT, UPDATE, or DELETE.

Can you chain multiple writable CTEs in one query?

A Yes, you can chain multiple writable CTEs

B No, only one writable CTE per query

C Only if they are INSERTs

D Only if they are SELECTs

PostgreSQL supports chaining multiple writable CTEs in a single query.

What is the main advantage of using writable CTEs?

A Make queries run faster always

B Automatically backup data

C Avoid using indexes

D Combine data modification and retrieval in one query

Writable CTEs let you modify data and use the results immediately in the same query.

Which statement is NOT valid inside a writable CTE?

A CREATE TABLE

B INSERT

C UPDATE

D SELECT

Writable CTEs support INSERT, UPDATE, DELETE, but not DDL statements like CREATE TABLE.

Explain how writable CTEs work in PostgreSQL and give an example of an UPDATE operation using a writable CTE.

Think about how you can update rows and then select them in one query.

Describe the benefits of using writable CTEs compared to running separate INSERT, UPDATE, or DELETE statements.

Consider how writable CTEs help organize queries.