3 ms·
>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 b
by croes 2y ago
>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.
- ibejoeb 2y agoI'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.
- croes 2y agoThanks for the explanation