What does standard SQL or any of the non-PostgreSQL SQLs do instead of RETURNING?

Common code for me:

if ($new_id = dbcall('INSERT INTO table (column) VALUES ($1) RETURNING id', 'meow'))

$new_id then contains the new id for the freshly inserted record.

According to https://www.postgresql.org/docs/current/sql-insert.html :


INSERT conforms to the SQL standard, except that the RETURNING clause is a PostgreSQL extension

That makes me feel bad about using RETURNING, and it also makes me wonder what standard SQL and non-PG SQL databases do to let the user "grab" the id in a safe manner.

I remember doing things like:

SELECT lastval();

directly after the INSERT, but that could easily turn into a "race condition" and thus give the wrong id! That's why I started doing RETURNING, after having it recommended to me by PG guys, but since it's nonstandard, I don't feel good about using it.

On the other hand, I have given up on ever writing "standard SQL" because such a thing doesn't seem to exist outside of the meeting room at some organization that charges money for this massive SQL standard which they somehow make money from even though nobody follows it.

The Standard SQL Way™ of generating IDs is to

• create a sequence for the table;

• read the next value with SELECT NEXT VALUE FOR seq_name¹; and then

• insert a complete row, including the ID value.

Before SQL:2003, there were no autogenerated IDs. And in SQL:2003, there appears to be no method to read the last GENERATED AS IDENTITY value.

¹ Postgres does not support this, and requires SELECT nextval('seq_name').

StackExchange Database Administrators Q#268664, answer score: 2