[jooz]

Some real life problems using standard SQL are:

- UPDATE does not return number of rows updated.

- There is no "upsert"

- There is no "merge"

- List all tables and explain command are not standard.

Do you plan to cover them on your project ?

[acarl005]

Actually standard SQL does specify a MERGE command (which lets you implement an upsert) and it's implemented in Postgres

[password4321]

MERGE debuted in PostgreSQL 15 fairly recently, and has some caveats.

https://news.ycombinator.com/item?id=33236780

[ttfkam]

SQL standards are a weird thing. Rather than wait for a spec and then get implementers like in the browser space (for the most part), SQL vendors will solve a particular customer's problem, submit their solution to the standards body, the committee will tweak the syntax for best coherence with the existing spec, and then other implementers may emerge.

This ends up with four slightly different syntaxes and the one that matches the standard wasn't even the one that proposed the feature in the first place! It is what it is. Bottom line: "close enough" is as good as it gets in the database arena, so choose your implementation wisely and go all in.

With that out of the way:

  • Multiple vendors support RETURNING from both INSERT and UPDATE statements.

  • Upserts exist with most vendors, typically with the ON CONFLICT DO variant.

  • Most support MERGE now in the latest versions.

  • I don't know of any popular database that lacks support for INFORMATION_SCHEMA queries, so listing all tables is in fact both in the standard and supported by all popular implementations.

[aerzen]

- PRQL is focused on analytical queries and it does not support DML (yet at least, see https://github.com/PRQL/prql/issues/1092). Thus there is no UPDATE or "upsert"

- What is merge? How is it different from join? How is it different from union?

- SQL does define `information_schema`, which can be used to list tables and columns. We had ideas about introspection and listing tables, but this is not on the roadmap yet. A probable outcome here will be improvements to tooling so you will be able to find tables via autocomplete (LSP).

- There are no plans for EXPLAIN

[dinedal]

> What is merge? How is it different from join? How is it different from union?

I believe the author is referencing https://www.essentialsql.com/difference-merge-update/ - which you answered in your first point. There's no UPDATE support yet, therefore there's no MERGE support yet.

[thwd]

Merging as a one-keyword feature for all flavors of SQL is almost impossible.

It gets hairy when you have columns with composite types. E.g. depending on database, records can be JSON objects, protobufs, structs, or other composite types, like tuples.

It is possible to define semantics to merge each of these, but they vary and the merge operation becomes verbose to allow to handle each weirdness of each of these underlying types.

Merging is also sensitive to NULL vs 0 vs a DEFAULT, plus in some databases, NULL is still allowed even when a column is marked NOT NULL.

You'd almost need a sub-language for the merging operation, specifying how to handle each corner case.

[ttfkam]

Merge mutates data. A join is simply a read from an associated set of data.

[mritchie712]

Almost every db I've used has `information_schema.tables`, doesn't that solve your list tables issue?