SQL differences

Upsert Needs ON CONFLICT on PostgreSQL and ON DUPLICATE KEY UPDATE on MySQL

Saving a setting that may or may not exist yet takes one statement on every engine, though not the same one on all four. SQLite and PostgreSQL use ON CONFLICT ... DO UPDATE, MySQL and MariaDB ON DUPLICATE KEY UPDATE.

REPLACE INTO works on SQLite, MySQL and MariaDB, but it deletes the old row and inserts a new one, which fires delete triggers and resets the columns the statement leaves out. Of these four engines, MERGE exists on PostgreSQL 15 and later only.

INSERT INTO t VALUES (1, 2) ON CONFLICT (id) DO UPDATE SET n = excluded.n; SELECT n FROM t, run on four engines: SQLite 3.45.1 2; PostgreSQL 16.15 2; MySQL 8.0.46 error: You have an error in your SQL syntax ... near 'CONFLICT (id) DO UPDATE SET n = excluded.n' at line 1; MariaDB 10.11.14 error: You have an error in your SQL syntax ... near 'CONFLICT (id) DO UPDATE SET n = excluded.n' at line 1.

What Each Engine Answered

On a table made with

CREATE TABLE t (id INTEGER PRIMARY KEY, n INTEGER);
INSERT INTO t VALUES (1, 1);

the example

INSERT INTO t VALUES (1, 2) ON CONFLICT (id) DO UPDATE SET n = excluded.n;
SELECT n FROM t;

gave these answers:

Engine Answer
SQLite 3.45.1 2
PostgreSQL 16.15 2
MySQL 8.0.46 error: You have an error in your SQL syntax ... near 'CONFLICT (id) DO UPDATE SET n = excluded.n' at line 1
MariaDB 10.11.14 error: You have an error in your SQL syntax ... near 'CONFLICT (id) DO UPDATE SET n = excluded.n' at line 1

Every Form BareSQL Checks

BareSQL Portable checks 5 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 What to write instead
ON CONFLICT DO UPDATE yes yes no no
ON DUPLICATE KEY UPDATE no no yes yes
REPLACE INTO yes no yes yes
INSERT OR REPLACE yes no no no
MERGE no yes no no The PostgreSQL answer holds from 15.

What to Write Instead

This form answers the same on every engine:

UPDATE t SET n = 2 WHERE id = 1;
INSERT INTO t (id, n) SELECT 1, 2 WHERE NOT EXISTS (SELECT 1 FROM t WHERE id = 1);
SELECT n FROM t;
Engine Answer
SQLite 3.45.1 2
PostgreSQL 16.15 2
MySQL 8.0.46 2
MariaDB 10.11.14 2

Update first, then insert what is missing: UPDATE t SET n = 2 WHERE id = 1, then INSERT INTO t (id, n) SELECT 1, 2 WHERE NOT EXISTS (SELECT 1 FROM t WHERE id = 1). Run both in one transaction.

What BareSQL Portable Reports

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

$ baresql lint --all example.sql
example.sql:3:29: error: ON CONFLICT ... DO UPDATE works on SQLite and PostgreSQL; MySQL and MariaDB refuse it. [upsert]
    ON CONFLICT(id) DO UPDATE
    Update first, then insert what is missing: UPDATE t SET n = 2 WHERE id = 1, then INSERT INTO t (id, n) SELECT 1, 2 WHERE NOT EXISTS (SELECT 1 FROM t WHERE id = 1). Run both in one transaction.

1 problem: 1 error.

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