Only thing cooler than CTEs are Recursive CTEs, but I struggle to find use cases where I can sneak one in.
this post was submitted on 12 Jun 2023
14 points (100.0% liked)
Programming
13368 readers
2 users here now
All things programming and coding related. Subcommunity of Technology.
This community's icon was made by Aaron Schneider, under the CC-BY-NC-SA 4.0 license.
founded 1 year ago
MODERATORS
I believe recursive CTEs are pretty cool for tree traversal. Anytime you've got a table with a foreign key on its own primary key they might come in handy.
I was skeptical of CTEs for a long time. I just used subqueries when I could in T SQL, and then I got a new job and my new company used Postgres. In the adaptation process I took a new look at CTEs and became a convert - it's just nicer and easier to read the intermediate step than as a subquery
CTEs are so helpful for me. They make complex queries much easier to construct and lets me ‘unit test’ the parts I’m working on.
Subqueries in Subqueries in Subqueries
Those work, but require a lot of careful structuring to get right, and can be a pain to debug. With a CTE, you can just call on the intermediate steps to trace down problems.
You can create a functional enum view by just assigning enums as the column names and storing a single row of the int (or whatever enum) representation.
Then use that view in a cross join. You can (almost) eliminate magic numbers entirely and makes the code much more human legible.
Yes, CTEs are awesome. Especially when you don't force materialization and the optimizer can work its magic.
I've had a lot of fun with window functions as well.
view more: next ›