21–30 of 63 posts
Very interesting site, but does anyone know why Oracle and mysql have a so poor support of the standard ? It looks as if postgres was the only one trying to comply.
What 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…
Postgres could do it long before its JSON capabilities, array_agg was added in Postgres 8.4 (2009) and hstore in 8.3 (2008)
What 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…
SELECT key, value1,
ARRAY(SELECT value2 FROM bar WHERE bar.key = foo.key)
AS value2_array
FROM foo;
(In Postgres; no "JSON capabilities" needed.)
What 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 looks like Postgres can do it [1] with its JSON capabilities Postgres could do it long before its JSON capabilities, array_agg was added in Postgres 8.4 (2009) and hstore in 8.3 (2008)
Very interesting site, but does anyone know why Oracle and mysql have a so poor support of the standard ? It looks as if postgres was the only one trying to comply.
Which seems to be a general with those two DBMS'...
If 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.)
Notably, MSSQL does optimize across CTEs, so there's not as much pressure to break out to views on that platform. And if you're feeling especially perverse, you can make a CTE a view, which sorta... sporks the problem.
If 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.)
Updatable 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…
Any reason to prefer that over INSERT INTO table (col, col) VALUES (v1, v2), (v1, v2), ... ?
Very interesting site, but does anyone know why Oracle and mysql have a so poor support of the standard ? It looks as if postgres was the only one trying to comply.
Have you read the SQL99 or even 2003 spec? No database supports everything. It's a matter which features one cherrypicks. If you want to use lots of XML and ORMs inside your database or use Java as stored procedures than SQL 2003 spec is great. The enterprise grade database software Oracle, DB2, MSSQL and Postgres are all somewhat comparable. MySQL with e.g. its InnoDB storage engine is very fast and web scale. Whoul…
For some use cases that's fine, sure. But I've also seen many developers come to regret choosing MySQL when, months or years down the line, they ran into issues that hadn't been apparent at first. And honestly I don't see how it's a "tradeoff" when Postgres can offer performance that's every bit the equal of MySQL.