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
We're a place where coders share, stay up-to-date and grow their careers.
We strive for transparency and don't collect excess data.