MarkusWinand

INSERT ... ON CONFLICT has fewer surprises in context of concurrency: https://modern-sql.com/caniuse/merge#illogical-errors

Besides portability, there is IMHO nothing against INSERT ... ON CONFLICT if it does what you need.

> Making a pretty diagram where all the behaviours from various DBMSs are listed is left as an exercise to the author. :)

It's there already!

It's in the chart as footnote "b": Outer reference must be the only argument (doesn’t support F441)

That's the one thing SQL Server doesn't eat. Those that are green in the chart work fine in this case.

No question, such a query should not be written. That's probably the reason why this odd behavior, which is even different in various DBMSes, is not causing everyday problems.

> SQLite also ignores size restrictions on text columns; [...] requires a trigger.

I'd go for CHECK constraints first: https://www.sqlite.org/lang_createtable.html#ckconst

> Procedural SQL [...] Choose your investment here carefully.

I think that the demand for procedual code has dropped drastically in the past decades as the "normal" SQL can solve so many more things with window functions, recursion, and so forth. So I'd say: Yes, choose your investment wisely and stay away from procedural SQL as long as possible.

The existing implementations (Oracle DB, SQL Server, MariaDB, Big Query) come with their problems too. I was a big fan of the new features when it came out in 2011, but pratically there is an unsolved elephant in the room: It doesn't cover schema changes.

Fair point, looks suspicious on first sight. I've checked my tests briefly: It's accepted as table and column name. As mentioned in footnote [0] I haven't checked different contextes yet.

Can you tell me: what kind of identifier is it (view name, function name) and which SQL context it causes problems (select list, create/drop statement, ...) and which system has problems with it. Thx.

Author of the website here.

All charts on modern-sql.com are backed by tests. I also keep them up-to-date by running them for new releases when they appear.

I wonder why you are so focused on SQL:2011? Since then there was SQL:2016 and now there is SQL:2023.

Also, which features in particular are you missing in PostgreSQL? Merge was added with PostgreSQL 15 a year ago: https://modern-sql.com/caniuse/merge

> The standard is not generally available. Most of us will never learn what is in it.

It also not aimed at users, but at implementors. Funny enough, they don't read the standard either ;) But more seriously: Some implementations are old and generally vendors prefer not changing the behavior of their product. When the standard comes later, the train has already departed. The most critical incompatibilities are in the elder features. The newer ones have a tendency the be more aligned with standard behavior (e.g. window functions are typically implemented just fine).

> They haven't gotten as far as working out variables or function composition either [0].

Part 2 SQL[0] is declarative and intentionally doesn't have variables. Part 4 SQL (aka "pl" SQL) does have variables. I personally consider Part 4 obsolete on systems that have sufficient modern Part 2 support.

> Tying SQL to your specific database is the best option for performance. Writing database-independent SQL is somethign of a fools errand, it is either trivial performance-insensitive enough that you should be using a code generator or complex enough to deserve DB-specific turning.

While this is certainly true for some cases there are also plenty of examples where the standard SQL is more concise and less error prone than product-specific alternatives. E.g. there COALESCE is the way to go rather than isnull, ifnull, nvl, or the like (typically limited to two arguments, sometimes strange treatment of different types).

There is a lot of *unnecessary* vendor-lock in in the field.

[0] https://modern-sql.com/standard/parts

I recently covered MERGE on modern-sql.com: https://modern-sql.com/caniuse/merge

There I also look at limitations of some implementations and problems such as not reporting ambiguous column names — just guessing what you mean ;)

The rationale behind the width is more the ease of reading than anything else: https://en.wikipedia.org/wiki/Line_length

But thanks for the feedback. There is a rework pending anyway.

I'm studying the SQL standard for years now and compared to other standards that I know (XSLT, a little CSS, decades ago POSIX, C and C++) the SQL standard is really hard to make sense of. You might overestimate the value of having access to it.

Having that said: free would be better.

> It comes down to how you plan to shard data and distribute queries when data doesn't fit on a single node.

A problem everbody would love to have but pretty much nobody actually has.

This is one of the questions I try to answer at https://modern-sql.com/

That particular one is already available in PostgreSQL 15.

https://modern-sql.com/caniuse/unique-nulls-not-distinct

Fixed. I wonder why Google sent me to http...

Luckily pretty much nobody needs the standard documents. It's actually my aim at https://modern-sql.com/ to make the relevant information more accessible — in particular including support-matrices ("Can I Use").

The major news are:

- SQL/PGQ - A Graph Query Language

- JSON improvements (a JSON type, simplified notations)

Peter Eisentraut gives a nice overview here: https://peter.eisentraut.org/blog/2023/04/04/sql-2023-is-fin...

See also: https://winand.at/conferences/past

Includes an updates version of the first slideset: https://modern-sql.com/slides/ModernSQL-2019-05-30.pdf

What Does ‘2ndQuadrant’ Mean?: https://www.2ndquadrant.com/en/what-does-2ndquadrant-mean/

The polymorphic view as I would like them would enable us to re-use entiere <query expressions> (i.e. "subqueries") applied to different data sources (i.e. "tables").

E.g (made-up syntax)

   CREATE TEMPLATE VIEW numbered_by_ts(t1) AS (
     SELECT t1.*
          , ROW_NUMBER() OVER(ORDER BY ts) AS rn
       FROM t1
   )

e.g. use it like this:

   SELECT *
     FROM numbered_by_ts(table_name);

I think they should behave more like C++ templates rather than C pre-processor makros[1], thus I used the keyword TEMPLATE in CREATE VIEW. If you provide a table that doesn't have a column TS, it would be a syntax error, just like it is with C++ templates.

With common table expressions it would be easily possible to inject entire subqueries into the template view:

   WITH cte AS (SELECT ... FROM ...)
   SELECT *
     FROM numbered_by_ts(cte);

For completness, WITH queries should also be allowed to be TEMPLATEs.

Speaking of the WITH clause, SQLite support ploymorphic views because SQLite CTEs are visible in elements that are "generally contained"[2] in the statement (the SQL standard defines it differently[3]).

Example:

   CREATE TABLE t1 (
    ts TIMESTAMP
   );
   INSERT INTO t1 VALUES ('2000-01-01 00:00:00');

   CREATE VIEW numbered_by_ts AS
   SELECT t1.*
        , ROW_NUMBER() OVER(ORDER BY ts) AS rn
     FROM t1
   ;

   CREATE TABLE ta (
    ts TIMESTAMP
   );
   INSERT INTO ta VALUES ('2010-01-01 00:00:00');

   CREATE TABLE tb (
    no_ts TIMESTAMP
   );
   INSERT INTO tb VALUES ('2020-01-01 00:00:00');

   SELECT * FROM numbered_by_ts;
   -- accesses t1, resturns year 2000

   WITH t1 AS (SELECT * FROM ta)
   SELECT * FROM numbered_by_ts;
   -- accesses ta, resturns year 2010

   WITH t1 AS (SELECT * FROM tb)
   SELECT * FROM numbered_by_ts;
   -- syntax error: no "ts" column in tb

SQLite is the only system I know of that implements WITH like that. See "views bypass with" here: https://modern-sql.com/feature/with#compatibility

The polymorpic table functions, as introduced by SQL:2016, might be able to accomplish all of that but for me they feel like using a sledgehamer for cracking a nut.

[1] As far as I understand the Oracle 20c SQL makros behave like C pre-processor makros.

[2] "Generally contain" as defined by "Syntactic containment" in ISO/IEC 9075-1. The 2011 version of that can be downloaded for free at ISO: http://standards.iso.org/ittf/PubliclyAvailableStandards/c05...

[3] ISO/IEC 9075-2, "<query expression>": the definition of "query name in scope" says "contained", not "generally contained".

Not only the grouping sets alone, the chaining of them.

E.g.

   GROUP BY tenant
          , GROUPING SETS ( (year, month)
                          , (year)
                          )

is equivalant to

   GROUP BY GROUPING SETS ( (tenant, year, month)
                          , (tenant, year)
                          )

and

   GROUP BY tenant, year
          , GROUPING SETS ( (month)
                          , ()
                          )

(however, I find the last one odd: I like to keep the "tenant" part seperate from the actual grupings you'd like to do).

Thx, updated.

The most important code management tool SQL has are views and the WITH clause.

https://modern-sql.com/use-case/literate-sql

Something like "polymorpihc" views would be extremley useful, but is not there yet (in standard SQL).

In other areas there are sublte features like the explicit WINDOW clause to avoid repeating similar OVER clauses. Breifly mentioned at the end of this doc:

https://www.postgresql.org/docs/current/tutorial-window.html

This was adopted in recent years by many FOSS-DBs, but not yet by the big commercial ones:

https://modern-sql.com/static/sm-blog-2019-postgesql-11.over...

Similarily the chaining of grouping sets can be helpful in those very rare cases you need them.

The Oracle Database intrdouced "SQL Macros" recently. For my taste, they are too 'rude' to make me happy on first sight (much like C #define):

https://blog.dbi-services.com/oracle-20c-sql-macros-a-scalar...

> for instance the order of conditions in the where clause matter if you want to leverage a multi-column index

Ups, if this is you take away, then I've done something wrong.

Let me correct that: The order of columns in an index matters, not the order of conditions in the where clause.

You are mixing two concepts here: 3VL and how most aggregates work in SQL: the drop NULL values before doing their work. That's why the first BOOL_AND example only sees one value, thus returning true.

> What SQL lacks (in this case), but the relational model not, is the ability to nest relations as easily as JSON.

SQL has nested relations since SQL:2003[1] (called multisets). They are not widley supported and frankly speaking I don't like them.

JSON was finally added with SQL:2016[2].

[1] https://sigmodrecord.org/publications/sigmodRecord/0403/E.Ji...

[2] https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016#js...

WITH is supported by all major SQL brands in the meanwhile.

https://modern-sql.com/feature/with#compatibility

Very well spotted! I've just fixed that.

One of my test cases was binding that paramter as 'numeric' rather than 'interger'. The other DBs didn't care about that.

Welcome. But db-engines.com is not my site (but also run by Austrians ;)