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 questies 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: http://peter.eisentraut.org/blog/2023/04/04/sql-2023-is-fini...