3 ms·

I agree with your further example cases (they were added after my comment). I was thinking more of the original post where someone was complaining about readab

by aidos 3y ago

I agree with your further example cases (they were added after my comment). I was thinking more of the original post where someone was complaining about readability when you have joins and subqueries - which is the usecase for CTEs.

mulmen 3y ago
Yeah sorry I edit a lot. I'll bump up my delay. Tables, views, and CTEs are all tools that can be used to make SQL more readable. They are all valid alternatives to subqueries in JOINs which is the only thing I would say you should "never" do. I tend to use all of them. I create tables with an optimized (typically star) schema for my purposes. My date dimensions are almost always views on top of a list of dates. I also use views to create a ~"feature store" that pre-joins the underlying star schema. I can then write "simple" analytical queries that utilize CTEs. Those CTEs tend to not have joins and they tend to only apply relevant filters so the final select is clear and concise.
- aidos 3y ago
  > CTEs aren't materialized so they're not tables. You might well know this, but one thing thats worth pointing out is that in Postgres you _can_ materialise the CTEs (though it's more to trick the planner when your use case is more about making sure everything to exploded and indexed in advance). with x as materialized (select ..) select ...
  - deleted 3y ago
    [deleted]
  - slt2021 3y ago
    neat trick! sometimes you absolutely need to materialize your CTE, especially if it is one big computation and you refer it 4-5 times downstream.
    - mulmen 3y ago
      According to the docs [1] this is not necessary: > All queries in the WITH list are computed. These effectively serve as temporary tables that can be referenced in the FROM list. A WITH query that is referenced more than once in FROM is computed only once, unless specified otherwise with NOT MATERIALIZED. [1]: https://www.postgresql.org/docs/15/sql-select.html https://www.postgresql.org/docs/15/sql-select.html
  - deleted 3y ago
    [deleted]

by aidos 3y ago

mulmen 3y ago

Yeah sorry I edit a lot. I'll bump up my delay. Tables, views, and CTEs are all tools that can be used to make SQL more readable. They are all valid alternatives to subqueries in JOINs which is the only thing I would say you should "never" do. I tend to use all of them. I create tables with an optimized (typically star) schema for my purposes. My date dimensions are almost always views on top of a list of dates. I also use views to create a ~"feature store" that pre-joins the underlying star schema. I can then write "simple" analytical queries that utilize CTEs. Those CTEs tend to not have joins and they tend to only apply relevant filters so the final select is clear and concise.

aidos 3y ago
> CTEs aren't materialized so they're not tables. You might well know this, but one thing thats worth pointing out is that in Postgres you _can_ materialise the CTEs (though it's more to trick the planner when your use case is more about making sure everything to exploded and indexed in advance). with x as materialized (select ..) select ...
- deleted 3y ago
  [deleted]
- slt2021 3y ago
  neat trick! sometimes you absolutely need to materialize your CTE, especially if it is one big computation and you refer it 4-5 times downstream.
  - mulmen 3y ago
    According to the docs [1] this is not necessary: > All queries in the WITH list are computed. These effectively serve as temporary tables that can be referenced in the FROM list. A WITH query that is referenced more than once in FROM is computed only once, unless specified otherwise with NOT MATERIALIZED. [1]: https://www.postgresql.org/docs/15/sql-select.html https://www.postgresql.org/docs/15/sql-select.html
- deleted 3y ago
  [deleted]