Upsert, row limits and identifier quoting across PostgreSQL, MySQL, SQLite and SQL Server

What it is

Database	Upsert	Row limiting	Identifier quoting
PostgreSQL	INSERT ... ON CONFLICT DO NOTHING / DO UPDATE SET ... (9.5+) — "This is also known as UPSERT"; MERGE exists from PostgreSQL 15	LIMIT n OFFSET m; the two-argument LIMIT m, n is rejected	double quotes "name" (SQL-standard)
MySQL / MariaDB	INSERT ... ON DUPLICATE KEY UPDATE ... — "an UPDATE of the old row occurs" on a unique-key clash	LIMIT m, n (offset first), or LIMIT n OFFSET m "for compatibility with PostgreSQL"	backtick `name` by default; double quotes only if ANSI_QUOTES SQL mode is enabled
SQLite	INSERT ... ON CONFLICT(...) DO NOTHING / DO UPDATE SET ... — "added to SQLite with version 3.24.0 (2018-06-04)"	LIMIT n OFFSET m, and also MySQL's LIMIT m, n with the offset first	accepts "name", `name` and [name], "included in SQLite for compatibility" with other engines; a double-quoted name that matches no column falls back to a string literal
SQL Server	no single-statement upsert keyword; closest built-in is MERGE ... WHEN MATCHED ... WHEN NOT MATCHED ...	ORDER BY ... OFFSET m ROWS FETCH NEXT n ROWS ONLY — only as part of an ORDER BY — from "SQL Server 2012 (11.x) and later"; older: TOP (n)	square brackets [name], or double quotes while QUOTED_IDENTIFIER is ON (the default for most client drivers)

Why it matters

Only PostgreSQL and SQLite share an upsert spelling, so a query generator that targets "SQL" in the abstract fails on at least two of these four engines. MySQL's ON DUPLICATE KEY UPDATE fires on any unique-index or primary-key clash, with no conflict target to name. SQLite followed PostgreSQL's ON CONFLICT spelling, but only from version 3.24.0 (June 2018) onward, so a query built against that syntax simply fails on an older embedded SQLite build; PostgreSQL-only forms such as ON CONFLICT ON CONSTRAINT name still do not carry over. Row-limiting has the same spread: MySQL's (and SQLite's) two-argument LIMIT offset, count puts the offset first and is rejected by PostgreSQL, while SQL Server didn't gain OFFSET/FETCH until the 2012 release and only accepts it after an ORDER BY. For identifiers, SQL-standard double quotes fail in MySQL's default mode, where they delimit strings unless ANSI_QUOTES is enabled, and in SQL Server sessions with QUOTED_IDENTIFIER OFF.

How to apply

• Write upsert logic per target database explicitly; only PostgreSQL and SQLite share a spelling, and even they differ in details.
• Check the minimum SQLite version any embedded consumer ships before relying on ON CONFLICT upsert syntax.
• Quote identifiers with double quotes for PostgreSQL/standard SQL targets, backticks for MySQL, and brackets for SQL Server.
• Prefer LIMIT n OFFSET m for MySQL and SQLite — it is also what PostgreSQL accepts — because the comma form reads the offset in the opposite position.

Pitfalls

• Copying an ON CONFLICT clause into MySQL, or an ON DUPLICATE KEY UPDATE clause into PostgreSQL.
• Assuming double-quoted identifiers work on MySQL without checking sql_mode.
• Assuming OFFSET/FETCH is available on any SQL Server instance regardless of version.