intermediateAdvanced SQLPostgreSQL Specific100% Free

CTEs in PostgreSQL

Definition

PostgreSQL CTEs support recursive queries, writeable CTE operations (INSERT/UPDATE/DELETE), and explicit MATERIALIZED/NOT MATERIALIZED hints for performance control.

Introduction to CTEs in PostgreSQL

PostgreSQL's CTE implementation is among the most feature-rich available. Unique to PostgreSQL are writeable CTEs that let you chain data-modifying statements, and the MATERIALIZED/NOT MATERIALIZED keywords added in PostgreSQL 12 for precise control over CTE execution.

Syntax

SQL
-- Writeable CTE (PostgreSQL-specific)
WITH moved_rows AS (
  DELETE FROM orders_old
  WHERE status = 'archived'
  RETURNING *
)
INSERT INTO orders_archive SELECT * FROM moved_rows;

-- Force materialization
WITH data AS MATERIALIZED (
  SELECT ... expensive query ...
)
SELECT * FROM data WHERE ...
UNION
SELECT * FROM data WHERE ...;

Examples

1

Writeable CTE — Move Records

-- Atomically move old orders to archive table
WITH archived AS (
  DELETE FROM orders
  WHERE created_at < NOW() - INTERVAL '2 years'
  RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM archived;

SELECT 'Archived: ' || COUNT(*) FROM archived;

Writeable CTEs allow DELETE + INSERT in a single atomic statement. The RETURNING clause captures deleted rows for the INSERT.

Try in Playground

Common Mistakes

Pre-PostgreSQL 12: CTEs were always materialized (could not be optimized as inline subqueries)

Forgetting CYCLE clause in recursive CTEs traversing cyclic graphs — causes infinite loops

Assuming writeable CTE side effects are visible within the same CTE — they are not

Frequently Asked Questions

Are PostgreSQL CTEs materialized?
In PostgreSQL 12+, CTEs are NOT MATERIALIZED by default (the optimizer can inline them). Before PostgreSQL 12, CTEs were always materialized (treated as optimization fences). You can control this explicitly with MATERIALIZED (force evaluation) or NOT MATERIALIZED (allow inlining) keywords.
What are writeable CTEs in PostgreSQL?
PostgreSQL CTEs can contain INSERT, UPDATE, or DELETE statements with RETURNING clauses. This enables complex atomic operations like moving records between tables, updating and returning changed rows, or chaining multiple data modifications in a single statement.

Related SQL Topics

Cte Common Table ExpressionsWindow Functions In PostgresqlJoins In PostgresqlTransactionsSubqueries

Practice This in the SQL Playground

Write real queries, see live results, and master CTEs in PostgreSQL hands-on. 100% free.

Open SQL PlaygroundBrowse All Lessons