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

21–30 of 32 posts

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

#21

Post by felixyz »

hansvm wrote: post #6

Used judiciously, a macro system in front of your SQL is often a good approach here. You gain the "linguistically common subtable" capabilities of a CTE, the ability to name constants, and the ability to name/parameterize common sub-expressions, even in databases not supporting CTEs. With LSP support being what it is, you can even whip up editor integration for your new language in a day or less. Compared to CTEs (as…

Any particular macro system you have used or created? If so, did you use an existing pre-processor, or just coded something up from scratch?

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

#23

Post by croes »

>The general idea is that it allows you to create something somewhat equivilant to a view that only exists during that transaction.

I can't talk for Postgres but on MS SQL it's definitely not like a view.

I had a CTE with a filter condition in the where close but got a conversion error in the later use for a value I already filtered out in the CTE.

So it may look like some kind of temporary view definition but the actual code the optimizer generates may behave unexpected.

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

#24

Post by ibejoeb »

croes wrote: post #23

>The general idea is that it allows you to create something somewhat equivilant to a view that only exists during that transaction. I can't talk for Postgres but on MS SQL it's definitely not like a view. I had a CTE with a filter condition in the where close but got a conversion error in the later use for a value I already filtered out in the CTE. So it may look like some kind of temporary view definition but the ac…

I'm all but certain that a CTE and an unindexed view are treated identically in the planning phase.

You're describing predicate pushdown, which can get hung up under certain conditions, like when using window functions or top. If I had to take a shot in the dark, you were dealing with something like that.

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

#25

Post by yas_hmaheshwari »

larodi wrote: post #19

I 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

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

#26

Post by croes »

ibejoeb wrote: post #24
croes wrote: post #23

>The general idea is that it allows you to create something somewhat equivilant to a view that only exists during that transaction. I can't talk for Postgres but on MS SQL it's definitely not like a view. I had a CTE with a filter condition in the where close but got a conversion error in the later use for a value I already filtered out in the CTE. So it may look like some kind of temporary view definition but the ac…

I'm all but certain that a CTE and an unindexed view are treated identically in the planning phase. You're describing predicate pushdown, which can get hung up under certain conditions, like when using window functions or top. If I had to take a shot in the dark, you were dealing with something like that.

Thanks for the explanation

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

#27

Post by simonw »

Dissori wrote: post #11

This was posted 4 days ago but now says 7 hours ago, even the comments from back then now show as 30 minutes old?

That happens when a Hacker News moderator decides to give a post a “second chance” at making it to the homepage. https://github.com/minimaxir/hacker-news-undocumented?tab=re...

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

#30

Post by camgunz »

silon42 wrote: post #17
camgunz wrote: post #5

Earlier quoted context omitted.

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.

I haven't had any, and yeah it's almost always the right thing IME.
Post reply on HN