Is CTE really a point of view?

Question

I always thought that CTE should be considered as an inline macro. Therefore, I think: if the CTE is not referenced / not used, it is not executed. This is just a definition, nothing more.

But take the following query:

create table t
(
    id int primary key
);

with
a as
(
    insert into t(id) values(1)
)
select false;

select * from t;

It seems that after the CTE-based query select * from treturns a tuple as it was inserted into the CTE. Why is this tuple inserted even though the CTE is not in use?

Is it by design or specification? Can you rely on this behavior? This allows you to execute multiple queries that are completely uncorrelated in a single query.

This seems to contradict the following information: https://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/#comment-19121

Answer 1

• CTE .

• . [ Postgres] , .

• [ Postgres] CTE ; ( ) CTE .

---

. - , CTE- CTE. CTE . CTE , -. CTE + CTE .

Answer 2

postgres, cte , , , . CTE , (INSERT/UPDATE/DELETE).

, , CTE , , , CTE, .

CTE , postgresql CTE .

.

WITH cte AS (SELECT * FROM foo WHERE foo.bar = True)
SELECT * FROM cte WHERE cte.id > 10 AND cte.id < 20

, postgresql

SELECT * FROM (SELECT * FROM foo WHERE bar = TRUE) cte
WHERE cte.id > 10 AND cte.id < 20

, . CTE

WITH cte AS (SELECT * FROM foo WHERE foo.bar = True AND foo.id > 10 AND foo.id < 20)
SELECT * FROM cte

, , .

CTE , SELECT. , CTE INSERT, UPDATE DELETE. , SQL.

, 9.5 INSERT ... ON CONFLICT CTE UPSERT. SO,

CTE, RECURSIVE CTE, CTE , , . , .