with has completely changed my usage of PostgreSQL. I am a newbie and being able to split queries into logical small steps with this has made SQL much more fun.
But don't you still get big long queries. Or can you make separate statements?
Modern SQL: With – Organize Complex Queries
31–40 of 63 posts
Re: Modern SQL: With – Organize Complex Queries
#32If your query is complex enough to warrant a (non-recursive) CTE, it's probably complex enough to warrant breaking that CTE out into a separate view (or function, if it performs a write). Now you can independently test and reuse that view, which you can't do with a CTE. (Bonus if you're in Postgres: Postgres optimizes across views, but not across CTEs. Go figure.)
Allow me to disagree. Before CTEs I would have agreed, but CTEs gain you the same in clarity (decomposition really helps with comprehensibility of complex queries) without the loss of having to chase down 5 different views in order to understand the one you're looking at.
My rule is to refactor into a view when and only when it's going to be reused.
> Postgres optimizes across views, but not across CTEs
Sadly true, and one of the few big gripes I have with Postgres. I half recall reading that they are reconsidering this decision, so there's hope.
Re: Modern SQL: With – Organize Complex Queries
#33Updatable CTEs are great, for example if you want to set a column to 1 where the date is the latest day for each value in a different column, you would do something like this ;WITH cte AS(SELECT ROW_NUMBER() OVER(PARTITION BY SomeVal ORDER BY SomeDate DESC) AS row, * FROM SomeTable) UPDATE cte SET id = CASE WHEN row = 1 THEN 1 ELSE 0 END Here is the DDL and DML in case you want to play around with this CREATE TABLE S…
So I've never come across INSERT table SELECT values UNION ALL SELECT values. Any reason to prefer that over INSERT INTO table (col, col) VALUES (v1, v2), (v1, v2), ... ?
No reason, I do use Row Value Constructor/Table Value Constructor, these were introduced in SQL Server 2008, while CTEs were introduced in SQL Server 2005, I think when I created this example initially SQL Server 2008 was just released so this would not have worked for a lot of people on 2005
Re: Modern SQL: With – Organize Complex Queries
#34What I really want from SQL is the ability to do a single select with one or more parts of each returned record being an array (defined by a one-to-many join) and others simple scalars. It looks like Postgres can do it [1] with its JSON capabilities, and to be honest its being a little while since I've dug into it, so things might have changed (I'm actually not even sure I'm phrasing the question correctly), but the…
Recursive CTE can be very fast in Postgres. I can create a closure table of 2 Million rows for indirect and direct relations i.e. All paths from 15000 source direct relations in about 12 seconds on my MBPro 1Tb ssd 16 Ram. The makes it practical to just recreate all paths as a materialzed view refresh instead of worring about complicated incremental graph logic such as moving branches. With such a closure view - any…
Can you post the code for this somewhere? Thanks!
Re: Modern SQL: With – Organize Complex Queries
#35Re: Modern SQL: With – Organize Complex Queries
#36If your query is complex enough to warrant a (non-recursive) CTE, it's probably complex enough to warrant breaking that CTE out into a separate view (or function, if it performs a write). Now you can independently test and reuse that view, which you can't do with a CTE. (Bonus if you're in Postgres: Postgres optimizes across views, but not across CTEs. Go figure.)
True in some cases, but I find CTEs invaluable for simplifying my ad-hoc/personal queries.
Re: Modern SQL: With – Organize Complex Queries
#37If your query is complex enough to warrant a (non-recursive) CTE, it's probably complex enough to warrant breaking that CTE out into a separate view (or function, if it performs a write). Now you can independently test and reuse that view, which you can't do with a CTE. (Bonus if you're in Postgres: Postgres optimizes across views, but not across CTEs. Go figure.)
Probably 30% of my queries are complex enough to have a CTE but none of them should be views. There is no reuse and views are opaque and exist in a single namespace. Views have their purpose but this isn't it.
Re: Modern SQL: With – Organize Complex Queries
#38Updatable CTEs are great, for example if you want to set a column to 1 where the date is the latest day for each value in a different column, you would do something like this ;WITH cte AS(SELECT ROW_NUMBER() OVER(PARTITION BY SomeVal ORDER BY SomeDate DESC) AS row, * FROM SomeTable) UPDATE cte SET id = CASE WHEN row = 1 THEN 1 ELSE 0 END Here is the DDL and DML in case you want to play around with this CREATE TABLE S…
So I've never come across INSERT table SELECT values UNION ALL SELECT values. Any reason to prefer that over INSERT INTO table (col, col) VALUES (v1, v2), (v1, v2), ... ?
As I very vaguely recall, not all DB systems have/had support for multiple VALUES statements.
Re: Modern SQL: With – Organize Complex Queries
#39Markus Winand is a great SQL expert. I'm really grateful for everything that he shares with us. He writes well, explains us the differences between the different SQL engines. And his books are fantastic references. I highly recommend his "No to offset" tutorial http://use-the-index-luke.com/no-offset Should be common knowledge, but I still see offset being used way to often, even in core ORM frameworks :-(
A summary: "don't use offset unless you need to be able to navigate to specific pages, in which case you have to use offset". That "navigate to specific pages" use case is pretty common.
Re: Modern SQL: With – Organize Complex Queries
#40Updatable CTEs are great, for example if you want to set a column to 1 where the date is the latest day for each value in a different column, you would do something like this ;WITH cte AS(SELECT ROW_NUMBER() OVER(PARTITION BY SomeVal ORDER BY SomeDate DESC) AS row, * FROM SomeTable) UPDATE cte SET id = CASE WHEN row = 1 THEN 1 ELSE 0 END Here is the DDL and DML in case you want to play around with this CREATE TABLE S…
So I've never come across INSERT table SELECT values UNION ALL SELECT values. Any reason to prefer that over INSERT INTO table (col, col) VALUES (v1, v2), (v1, v2), ... ?
You'll find INSERT SELECT UNION ALL interesting when you want to insert rows from multiple table sources. For example, when refactoring a two tables into a single table or loading data into a temporary table (BCP) before copying it into a target table.