Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Postgres feature you're not using – CTEs a.k.a. WITH clauses

craigkerstiens.com

Post reply on HN
Print view View original

11–20 of 32 posts

Re: Postgres feature you're not using – CTEs a.k.a. WITH clauses

#12

Post by beAbU »

(I'm not a dev)

This feels to me like a "in other news, water is wet" kind of story. Maybe it's just me, but based on the other comments here CTEs seem to be common knowledge. I went on a SQL trial by fire this year after landing in a role that required massive amounts of weird querying for ad-hoc reporting purposes. Before this my experience and knowledge stopped at different join types.

I naturally discovered CTEs over time after seeking solutions to my problems on the usual online resources. For me the use is two-fold. 1/ I use it to create ad-hoc lookups on data, where the lookups are not available in the DB in the first place. And 2/ In complex queries with lots and lots of conditional joins, I can use a CTE to basically build up my temporary base table, getting all my data in one place, then my main `SELECT` becomes a lot cleaner and easier to read.

I've not looked at recursive CTEs yet, supposedly they can be quite handy. Not sure for what though.

We recently migrated from MySQL to Postgres, and the data reporting went from direct-db querying to Snowflake. And CTEs work on for me on all 3 platforms, so this does not even seem like a Postgres only thing.

Re: Postgres feature you're not using – CTEs a.k.a. WITH clauses

#15

Post by zaxomi »

cesarb wrote: post #14

WITH clauses are a somewhat recent feature, at least on PostgreSQL. If you learned SQL before they became commonly available, it's unsurprising if you tend to think of other SQL techniques first.

A quick search shows it in the documentation for version 8.4, released July 1, 2009.

Maybe 15 years is "somewhat recent feature". :)

Re: Postgres feature you're not using – CTEs a.k.a. WITH clauses

#17

Post by silon42 »

camgunz wrote: post #5
hyperman1 wrote: post #3

Earlier quoted context omitted.

This changed in pg 12. https://www.postgresql.org/docs/release/12.0/ : Automatic (but overridable) inlining of common table expressions (CTEs)

Yep, it's a huge improvement for complicated pipelines. One thing to be aware of is "NOT MATERIALIZED". Here's what the docs say: A useful property of WITH queries is that they are normally evaluated only once per execution of the parent query, even if they are referred to more than once by the parent query or sibling WITH queries. Thus, expensive calculations that are needed in multiple places can be placed within a…

Any issues with recursive and NOT MATERIALIZED? Recursive is pretty much the reason to use CTEs for me and NOT MATERIALIZED is essential there in most cases.

Re: Postgres feature you're not using – CTEs a.k.a. WITH clauses

#18

Post by cesarb »

zaxomi wrote: post #15
cesarb wrote: post #14

WITH clauses are a somewhat recent feature, at least on PostgreSQL. If you learned SQL before they became commonly available, it's unsurprising if you tend to think of other SQL techniques first.

A quick search shows it in the documentation for version 8.4, released July 1, 2009. Maybe 15 years is "somewhat recent feature". :)

Well, it wasn't there when I first learned SQL by reading the PostgreSQL manual. I recall that this was back when the size of TEXT columns was limited, so it was before version 7.1 (which introduced TOAST tables), released 13 April, 2001.

So yeah, if you first learned SQL around 25 years ago, something added only 15 years ago is "somewhat recent". And according to a link posted in another comment, other databases introduced this feature (non-recursive WITH clauses) even later.

Post reply on HN