Modern SQL: With – Organize Complex Queries
modern-sql.com
Modern SQL: With – Organize Complex Queries
1–10 of 63 posts
Re: Modern SQL: With – Organize Complex Queries
#2Nice reference.
Re: Modern SQL: With – Organize Complex Queries
#3What 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 old way of doing a single select and then looping over it in whatever code is calling the SQL and doing N more selects, one for each row to get the sub-array is horrible.
[1] http://bender.io/2013/09/22/returning-hierarchical-data-in-a...
Re: Modern SQL: With – Organize Complex Queries
#4Wow, what I liked on this website is that you can turn off social plugins, why not more websites do that...
Re: Modern SQL: With – Organize Complex Queries
#5What 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…
What you need is good old array_agg(expression) with group by.
Json capabilities may be needed for more complex hierarchical structures.
Re: Modern SQL: With – Organize Complex Queries
#6What 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…
In the old days, SQL operated on rectangular tables and produced rectangular tables ... which doesn't work so well for data that's better modelled as a tree or graph. But there now lots of ways to produced multi-valued output and to operate on graph-structured data. In addition to PG's JSON, you might checkout recursive unions - which supports a subset of graph navigation. But while SQL may have grown just a tad more expressive, I'm not sure that performance is up to snuff. And even if it's now possible to express some things in SQL, it doesn't mean it's easy or readable.
Re: Modern SQL: With – Organize Complex Queries
#7Wow, what I liked on this website is that you can turn off social plugins, why not more websites do that...
It's actually "opt-in" (=default off) and common in Europe.
A German IT media website (heise.de) pioniered that approach in 2011: https://web.archive.org/web/20150318081341/http://www.heise.... ; the newer code: https://web.archive.org/web/20150424102213/http://www.heise....
Re: Modern SQL: With – Organize Complex Queries
#8The optimization fence in PostgreSQL is usually bad for SELECTs but it has it's own benefits. See this thread http://www.postgresql.org/message-id/201209191305.44674.db@k...
Re: Modern SQL: With – Organize Complex Queries
#9with 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.
Re: Modern SQL: With – Organize Complex Queries
#10What 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…
It works pretty well and fairly quickly. We use the array_agg and other json features in a Postgres to Elasticsearch ETL. Postgres is able to spit out the exact format that we need, so the application code isn't really doing much more than coordinating the reads and writes.