SQL dialect comparison

PostgreSQL

LIMIT 20 OFFSET 40

MySQL

LIMIT 40, 20

SQL Server

SELECT TOP 20 * FROM t ORDER BY ... OFFSET 40 ROWS

SQLite

LIMIT 20 OFFSET 40

SQL Server's TOP goes right after SELECT, not at the end.

PostgreSQL

a || b

MySQL

CONCAT(a, b)

SQL Server

a + b   -- or CONCAT(a, b)

SQLite

a || b

|| is standard SQL; MySQL reads it as logical OR by default, so use CONCAT there.

PostgreSQL

now()   -- or CURRENT_TIMESTAMP

MySQL

NOW()

SQL Server

GETDATE()

SQLite

datetime('now')

CURRENT_DATE / CURRENT_TIMESTAMP are the standard forms most engines also accept.

PostgreSQL

date_trunc('month', ts)

MySQL

DATE_FORMAT(ts, '%Y-%m-01')

SQL Server

DATETRUNC(month, ts)   -- 2022+

SQLite

strftime('%Y-%m', ts)

This is the least portable corner of SQL — expect a different function name everywhere.

PostgreSQL

ts + INTERVAL '7 days'

MySQL

DATE_ADD(ts, INTERVAL 7 DAY)

SQL Server

DATEADD(day, 7, ts)

SQLite

date(ts, '+7 days')

Store timestamps in UTC and do this math before formatting for display.

PostgreSQL

INSERT ... ON CONFLICT (id) DO UPDATE SET ...

MySQL

INSERT ... ON DUPLICATE KEY UPDATE ...

SQL Server

MERGE INTO t USING src ON ... WHEN MATCHED ...

SQLite

INSERT ... ON CONFLICT (id) DO UPDATE SET ...

Only PostgreSQL and SQLite share syntax here. MySQL and standard SQL/SQL Server are both genuinely different shapes — see the full page.

PostgreSQL

id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

MySQL

id INT AUTO_INCREMENT PRIMARY KEY

SQL Server

id INT IDENTITY(1,1) PRIMARY KEY

SQLite

id INTEGER PRIMARY KEY

SQLite doesn't need a keyword at all — INTEGER PRIMARY KEY is an alias for the internal rowid and auto-increments on its own.

PostgreSQL

col ILIKE 'a%'

MySQL

col LIKE 'a%'  -- ci collation by default

SQL Server

col LIKE 'a%'  -- ci collation by default

SQLite

col LIKE 'a%'  -- ASCII case-insensitive by default

SQL Server and MySQL's default collations are usually already case-insensitive — this is for when they aren't, or you need it explicitly.

PostgreSQL

POSITION('cd' IN s)   -- or STRPOS(s, 'cd')

MySQL

INSTR(s, 'cd')

SQL Server

CHARINDEX('cd', s)

SQLite

instr(s, 'cd')

All 1-indexed; 0 means not found. Watch the argument order — POSITION reads needle-in-haystack, the others read haystack-then-needle.

PostgreSQL

SUBSTRING(s FROM 2 FOR 3)

MySQL

SUBSTRING(s, 2, 3)

SQL Server

SUBSTRING(s, 2, 3)

SQLite

substr(s, 2, 3)

Positions are 1-indexed everywhere.

PostgreSQL

SPLIT_PART(s, ',', 2)

MySQL

SUBSTRING_INDEX(s, ',', 2)

SQL Server

SELECT value FROM STRING_SPLIT(s, ',')

SQLite

no built-in — instr()/substr() by hand

No standard form at all, and SQLite has nothing built in — see the full page for a workaround.

PostgreSQL

a IS NOT DISTINCT FROM b

MySQL

a <=> b

SQL Server

(a = b) OR (a IS NULL AND b IS NULL)

SQLite

a IS NOT DISTINCT FROM b

Standard = treats two NULLs as unequal (unknown, actually). These operators say "yes, both NULL counts as equal."

PostgreSQL

EXPLAIN [ANALYZE] SELECT ...

MySQL

EXPLAIN [ANALYZE] SELECT ...   -- 8.0.18+ for ANALYZE

SQL Server

SET STATISTICS PROFILE ON

SQLite

EXPLAIN QUERY PLAN SELECT ...

"ANALYZE" variants actually execute the query — not a dry run on a mutating statement.

PostgreSQL

BEGIN;

MySQL

START TRANSACTION;

SQL Server

BEGIN TRANSACTION;

SQLite

BEGIN;

COMMIT / ROLLBACK end it the same way on all four.

PostgreSQL

"Order Date"

MySQL

`Order Date`

SQL Server

[Order Date]

SQLite

"Order Date"   -- also accepts [ ] and `

Mixing these up is one of the most common cross-engine porting errors — a query with "col" fails outright on MySQL.

PostgreSQL

WITH RECURSIVE r AS (...) SELECT ...

MySQL

WITH RECURSIVE r AS (...) SELECT ...   -- 8.0.1+

SQL Server

WITH r AS (...) SELECT ...   -- no RECURSIVE keyword needed

SQLite

WITH RECURSIVE r AS (...) SELECT ...

SQL Server infers recursion from the CTE referencing itself — it doesn’t use the RECURSIVE keyword at all.