Inserts and Updates

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 …

October 26, 2026

SQL differences

MySQL Refuses an UPDATE Whose Subquery Reads the Same Table

Marking rows with UPDATE t SET flag = 1 WHERE id IN (SELECT id FROM t WHERE ...) works on SQLite, PostgreSQL and MariaDB. MySQL refuses an UPDATE or DELETE whose subquery …

October 26, 2026

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 …

October 26, 2026

SQL differences

SQLite Has No TRUNCATE Statement

Test setups often empty their tables with TRUNCATE TABLE between runs. PostgreSQL, MySQL and MariaDB have it, and SQLite refuses it. SQLite empties a table quickly with a …

October 26, 2026

SQL differences

UPDATE With a Join Needs Different Syntax on MySQL and PostgreSQL

Copying prices from a staging table with a join takes UPDATE ... FROM on SQLite and PostgreSQL and UPDATE ... JOIN on MySQL and MariaDB, and neither runs on all four. The …

October 26, 2026

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 …

October 26, 2026