SQL differences

MySQL Refuses INSERT RETURNING While PostgreSQL and SQLite Accept It

INSERT ... RETURNING id hands back the new row’s id in the same round trip on SQLite, PostgreSQL and MariaDB. MySQL has no RETURNING and refuses the statement.

SQLite added RETURNING in version 3.35, and MariaDB in 10.5 for INSERT and DELETE (MariaDB refuses it on UPDATE). Each engine also has its own function for the last generated id: LAST_INSERT_ID() on MySQL and MariaDB, last_insert_rowid() on SQLite and lastval() on PostgreSQL.

INSERT INTO t VALUES (1, 5) RETURNING id, run on four engines: SQLite 3.45.1 1; PostgreSQL 16.15 1; MySQL 8.0.46 error: You have an error in your SQL syntax ... near 'RETURNING id' at line 1; MariaDB 10.11.14 1.

What Each Engine Answered

On a table made with

CREATE TABLE t (id INTEGER, x INTEGER);

the example

INSERT INTO t VALUES (1, 5) RETURNING id;

gave these answers:

Engine Answer
SQLite 3.45.1 1
PostgreSQL 16.15 1
MySQL 8.0.46 error: You have an error in your SQL syntax ... near 'RETURNING id' at line 1
MariaDB 10.11.14 1

Every Form BareSQL Checks

BareSQL Portable checks 3 forms of this, and each was run on all four engines. yes means the engine ran it and the engines that ran it agreed, no means the engine refused it, and a value is what the engine answered where they disagreed.

Form SQLite PostgreSQL MySQL MariaDB
INSERT ... RETURNING yes yes no yes
UPDATE ... RETURNING yes yes no no
DELETE ... RETURNING yes yes no yes

What to Write Instead

This form answers the same on every engine:

INSERT INTO t VALUES (1, 5);
SELECT id FROM t WHERE x = 5;
Engine Answer
SQLite 3.45.1 1
PostgreSQL 16.15 1
MySQL 8.0.46 1
MariaDB 10.11.14 1

Read the new id through the driver, which has a call for it on every engine, or select the row again by a unique key after the insert.

What BareSQL Portable Reports

Saved as example.sql, the example gets this from baresql lint --all:

$ baresql lint --all example.sql
example.sql:2:29: error: INSERT ... RETURNING works on SQLite, PostgreSQL and MariaDB; MySQL refuses it. [returning]
    RETURNING
    Read the new id through the driver, which has a call for it on every engine, or select the row again by a unique key after the insert.

1 problem: 1 error.

baresql rules returning prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.