SQL differences

INSERT IGNORE Becomes INSERT OR IGNORE on SQLite and ON CONFLICT DO NOTHING on PostgreSQL

Loading a file of events that may hold rows you already have calls for an insert that skips duplicates. MySQL and MariaDB spell it INSERT IGNORE and SQLite INSERT OR IGNORE, while PostgreSQL and SQLite take ON CONFLICT DO NOTHING.

Each form skips rows that would break a unique key, and each fails on the engines that use another one. INSERT IGNORE also hides other errors on MySQL and MariaDB, such as values that do not fit the column.

INSERT IGNORE INTO t VALUES (1); SELECT COUNT(*) FROM t, run on four engines: SQLite 3.45.1 error: near "IGNORE": syntax error; PostgreSQL 16.15 error: syntax error at or near "IGNORE"; MySQL 8.0.46 1; MariaDB 10.11.14 1.

What Each Engine Answered

On a table made with

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

the example

INSERT IGNORE INTO t VALUES (1);
SELECT COUNT(*) FROM t;

gave these answers:

Engine Answer
SQLite 3.45.1 error: near "IGNORE": syntax error
PostgreSQL 16.15 error: syntax error at or near "IGNORE"
MySQL 8.0.46 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 IGNORE no no yes yes
INSERT OR IGNORE yes no no no
ON CONFLICT DO NOTHING yes yes no no

What to Write Instead

This form answers the same on every engine:

INSERT INTO t (id) SELECT 1 WHERE NOT EXISTS (SELECT 1 FROM t WHERE id = 1);
SELECT COUNT(*) FROM t;
Engine Answer
SQLite 3.45.1 1
PostgreSQL 16.15 1
MySQL 8.0.46 1
MariaDB 10.11.14 1

Insert from a SELECT that checks first: INSERT INTO t (id) SELECT 1 WHERE NOT EXISTS (SELECT 1 FROM t WHERE id = 1). When two sessions insert the same row at once, the unique key still stops the second one, with an error instead of a skip.

What BareSQL Portable Reports

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

$ baresql lint --all example.sql
example.sql:3:1: error: INSERT IGNORE works on MySQL and MariaDB; SQLite and PostgreSQL refuse it. [insert-ignore]
    INSERT IGNORE
    Insert from a SELECT that checks first: INSERT INTO t (id) SELECT 1 WHERE NOT EXISTS (SELECT 1 FROM t WHERE id = 1). When two sessions insert the same row at once, the unique key still stops the second one, with an error instead of a skip.

1 problem: 1 error.

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