It would be convenient, for example in T389028, to use native upsert on non-MySQL DBMSs. There are a number of difficulties which I will document here.
Insert ID
The existing emulation goes to a fair bit of trouble to set the insert ID to the ID of the updated row.
In MySQL, insertId() after a native upsert returns the ID of the updated row. In SQLite, the last insert ID is not modified by an upsert following the update path, so insertId() will give you garbage — this matches MySQL's LAST_INSERT_ID() but not mysql_insert_id(). In PostgreSQL, a native upsert increments the underlying sequence even when it follows the update path, and insertId() returns the updated sequence value.
MariaDB 10.11:
MariaDB> CREATE TABLE t1 (a INTEGER PRIMARY KEY AUTO_INCREMENT, b INTEGER, c INTEGER); MariaDB> CREATE UNIQUE INDEX b ON t1 (b); MariaDB> CREATE TABLE t2 (a INTEGER PRIMARY KEY AUTO_INCREMENT, b INTEGER, c INTEGER); MariaDB> CREATE UNIQUE INDEX b ON t2 (b);
If we update table t2 with an upsert, LAST_INSERT_ID() returns the value from the insert into table t1:
MariaDB> INSERT INTO t2 (b, c) VALUES (1,1); Query OK, 1 row affected (0.009 sec) MariaDB> INSERT INTO t1 (b, c) VALUES (1,1); Query OK, 1 row affected (0.010 sec) MariaDB> INSERT INTO t1 (b, c) VALUES (2,2); Query OK, 1 row affected (0.008 sec) MariaDB> INSERT INTO t1 (b, c) VALUES (3,3); Query OK, 1 row affected (0.008 sec) MariaDB> INSERT INTO t2 (b, c) VALUES (1,1) ON DUPLICATE KEY UPDATE c=c+1; Query OK, 2 rows affected, 1 warning (0.019 sec) MariaDB> SELECT last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 3 | +------------------+ 1 row in set (0.001 sec)
SQLite's matches MariaDB's in this test:
sqlite> INSERT INTO t2 (b, c) VALUES (1,1); sqlite> INSERT INTO t1 (b, c) VALUES (1,1); sqlite> INSERT INTO t1 (b, c) VALUES (2,2); sqlite> INSERT INTO t1 (b, c) VALUES (3,3); sqlite> INSERT INTO t2 (b, c) VALUES (1,1) ON CONFLICT (b) DO UPDATE SET c=c+1; sqlite> SELECT last_insert_rowid(); 3
Postgres increments the underlying sequence before attempting to insert the row and leaves gaps in the table:
postgres> INSERT INTO t2 (b, c) VALUES (1,1) ON CONFLICT (b) DO UPDATE SET c=t2.c+1;
INSERT 0 1
postgres> INSERT INTO t2 (b, c) VALUES (1,1) ON CONFLICT (b) DO UPDATE SET c=t2.c+1;
INSERT 0 1
postgres> INSERT INTO t2 (b, c) VALUES (1,1) ON CONFLICT (b) DO UPDATE SET c=t2.c+1;
INSERT 0 1
postgres> SELECT lastval();
lastval
---------
3
(1 row)
postgres> INSERT INTO t2 (b, c) VALUES (4,4);
INSERT 0 1
postgres> SELECT * from t2;
a | b | c
---+---+---
1 | 1 | 3
4 | 4 | 4
(2 rows)Maybe the simplest solution is to ensure that there is no calling code that depends on the value of insertId() after upsert(). Note that the MariaDB upsert gave a warning on my system:
MariaDB> SHOW WARNINGS\G *************************** 1. row *************************** Level: Note Code: 1592 Message: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe 1 row in set (0.000 sec)
So what is the use case for insertId() after upsert? If you insert with a fixed value for the ID and it conflicts, you know what it was because you specified it. If you insert without specifying an ID and it conflicts on another unique index, that's unsafe for replication.
Field name ambiguity
postgres> INSERT INTO t2 (b) VALUES (1) ON CONFLICT (b) DO UPDATE SET c=c+1; ERROR: column reference "c" is ambiguous LINE 1: ... INTO t2 (b) VALUES (1) ON CONFLICT (b) DO UPDATE SET c=c+1;
The equivalent of this very simple query works in MariaDB and SQLite but fails in Postgres because the excluded and target rows are both available in the expression with the same field names. It seems like a bug. The excluded and target rows are necessarily from the same table, so you can never give an unqualified column name in the expression.
Maybe in PostgreSQL 15+ it will be possible to do something like
MERGE INTO t2 USING (SELECT 1 AS b) AS source ON b=source.b WHEN MATCHED THEN UPDATE b=b+1 WHEN NOT MATCHED THEN INSERT (b) VALUES (b);
EXCLUDED table alias
In SQLite and PostgreSQL, EXCLUDED is reserved in the SET expression and can't be used to reference a table. I couldn't find anything in Code Search using it as a table name.