It's strange to call CTEs a "postgres feature". It's standard SQL, it's in all databases.
Not in MySQL 5.7, which is officially my least favorite database to maintain.
Postgres feature you're not using – CTEs a.k.a. WITH clauses
31–32 of 32 posts
Re: Postgres feature you're not using – CTEs a.k.a. WITH clauses
#32I sure do use them for many years now, my girlfriend now uses them and loves them, my topcoder friend eventually started using them 10 years ago. So, no, we are using them , but y’all know what ? Unless recursive they are really nothing so special…
You are right about recursive CTE - that's a functionality that's not possible without CTE However, I will argue that an equally great benefit is code readability. That allows you to give good names to pieces of SQL block is like having small functions in normal programming
Indeed it is, although as cascade of create temp view statements within transaction works almost the same way for readability. besides, not everyone can count the open/closing braces for the CTE parts.
CTEs also cannot have indexes on the intermediate results, while views/mviews can and this benefits great cascaded analysis in SQL.
speaking from experience, where we had to optimize a particular network analysis for urban planning, which was initially impossible in qgis/argis, then first version ran for 3-10mins, and initially with intermediate indices the whole thing went down to 45sec-1.5min. this is not possible with CTE to my knowledge, even though I've searched for ways to tell it to build intermediate indices.