SQL CTE "relation does not exist" scope error

TL;DR

A CTE exists only inside the single statement that defines it, so referencing it from a second statement or from outside its query fails with relation does not exist. Fix it by putting all references inside the one WITH query, or promote the CTE to a temp table when multiple statements need it.

ERROR: relation "recent_orders" does not exist

Use this when

• A WITH query runs, but a follow-up query using the CTE name fails
• The CTE name looks right but the error says it doesnt exist
• You split one big query into two statements and the second broke

Not for this skill when

• The table genuinely doesnt exist (check the schema)
• A temp table vanishes between sessions (thats session scope)
• A recursive CTE loops forever (thats a termination problem)

Steps

1. Confirm the shape of the bug with a minimal repro:

WITH recent_orders AS (SELECT * FROM orders WHERE created_at > now() - interval '7 days')
SELECT count(*) FROM recent_orders;
-- then, separately:
SELECT count(*) FROM recent_orders;

Expected output: the first statement works, the second fails with relation "recent_orders" does not exist. The CTE died with its statement.

2. Keep everything that touches the CTE inside the single WITH statement:

WITH recent_orders AS (
  SELECT * FROM orders WHERE created_at > now() - interval '7 days'
)
SELECT status, count(*)
FROM recent_orders
GROUP BY status;

Expected output: the aggregation works. One statement, one scope, no error.

3. Chain multiple CTEs when later logic needs earlier results:

WITH recent_orders AS (
  SELECT * FROM orders WHERE created_at > now() - interval '7 days'
),
order_totals AS (
  SELECT customer_id, SUM(amount) AS total FROM recent_orders GROUP BY customer_id
)
SELECT * FROM order_totals WHERE total > 1000;

Expected output: the filtered totals. Later CTEs can reference earlier ones, all within the same statement.

4. When several separate statements need the same result, use a temp table instead:

CREATE TEMP TABLE recent_orders AS
SELECT * FROM orders WHERE created_at > now() - interval '7 days';
SELECT count(*) FROM recent_orders;
SELECT status, count(*) FROM recent_orders GROUP BY status;

Expected output: both statements work. Temp tables live for the session, so any statement in the session can see them.

5. Check for name shadowing if the CTE is defined but still not found:

SELECT tablename FROM pg_tables WHERE tablename = 'recent_orders';

Expected output: empty means no shadowing. If a real table with the CTEs name exists in a schema earlier in search_path, some engines resolve the real table instead; rename the CTE to something unambiguous.

Variant phrasings

WITH clause relation does not exist

The classic split-statement bug from step 1. CTEs are statement-scoped, full stop.

CTE not visible in subquery

A CTE is visible to subqueries inside its own statement, including nested ones. If it isnt visible, the subquery is actually a separate statement (check for stray semicolons) or a function boundary.

can you reference a CTE twice

Yes, within the same statement you can reference a CTE as many times as you like. Each reference may re-execute it though; on Postgres 12+ use MATERIALIZED to control that.

Why it happens

SQLs WITH clause is syntactic sugar: the CTE name is a temporary named subquery that the planner inlines or materializes within one statement. It is not a catalog object, so once the statement finishes there is nothing left to resolve the name against, and the next statement fails exactly as if you had typed a nonexistent table.

Edge cases

• CTEs inside a VIEW definition are fine; the view stores the whole statement including the WITH clause.
• DDL cant reference CTEs: CREATE TABLE t AS WITH ... works (the WITH is part of the SELECT), but a bare WITH before DDL does nothing useful.
• Recursive CTEs follow the same scoping; the RECURSIVE keyword doesnt extend visibility.
• Some drivers split on semicolons client-side, turning one script into many statements and breaking CTEs that span them; send it as one statement.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_ZId-ar6thrXia9ThX-TE8g