re: SQL 201: Why you should use SQL CTEs VIEW POST

FULL DISCUSSION
 

It's worth noting that in PostgreSQL a CTE is an "optimiser fence", in that the query that is contains is executed without further optimisation. So if you apply a further predicate to the CTE result in another CTE or the main query, this predicate isn't pushed down to the original CTE.

On the other hand, in PostgreSQL a CTE can execute an insert, update or delete, and return values from the affected rows for use later on in the same query, which is very powerful.

 

Note - CTEs are no longer an optimizer fence in PostgreSQL 12

 

Wow, that's a game changer. Thanks for sharing

code of conduct - report abuse