T-SQL vs. Postgres, MySQL, SQLite

SQL Server

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

PostgreSQL

LIMIT 20 OFFSET 40

MySQL

LIMIT 40, 20

SQLite

LIMIT 20 OFFSET 40

TOP goes right after SELECT, not at the end — and needs ORDER BY to be meaningful.

SQL Server

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

PostgreSQL

a || b

MySQL

CONCAT(a, b)

SQLite

a || b

T-SQL uses + (or CONCAT); MySQL reads || as logical OR by default unless PIPES_AS_CONCAT is set.

SQL Server

GETDATE()

PostgreSQL

now()   -- or CURRENT_TIMESTAMP

MySQL

NOW()

SQLite

datetime('now')

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

SQL Server

DATETRUNC(month, ts)   -- 2022+

PostgreSQL

date_trunc('month', ts)

MySQL

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

SQLite

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

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

SQL Server

DATEADD(day, 7, ts)

PostgreSQL

ts + INTERVAL '7 days'

MySQL

DATE_ADD(ts, INTERVAL 7 DAY)

SQLite

date(ts, '+7 days')

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

SQL Server

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

PostgreSQL

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

MySQL

INSERT ... ON DUPLICATE KEY UPDATE ...

SQLite

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

MERGE is the most different shape here. Postgres and SQLite share ON CONFLICT syntax; MySQL has its own ON DUPLICATE KEY UPDATE — see the full page.

SQL Server

id INT IDENTITY(1,1) PRIMARY KEY

PostgreSQL

id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

MySQL

id INT AUTO_INCREMENT 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.

SQL Server

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

PostgreSQL

col ILIKE 'a%'

MySQL

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.

SQL Server

CHARINDEX('cd', s)

PostgreSQL

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

MySQL

INSTR(s, 'cd')

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.

SQL Server

SUBSTRING(s, 2, 3)

PostgreSQL

SUBSTRING(s FROM 2 FOR 3)

MySQL

SUBSTRING(s, 2, 3)

SQLite

substr(s, 2, 3)

Positions are 1-indexed everywhere.

SQL Server

SELECT value FROM STRING_SPLIT(s, ',')

PostgreSQL

SPLIT_PART(s, ',', 2)

MySQL

SUBSTRING_INDEX(s, ',', 2)

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.

SQL Server

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

PostgreSQL

a IS NOT DISTINCT FROM b

MySQL

a <=> b

SQLite

a IS NOT DISTINCT FROM b

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

SQL Server

SET STATISTICS PROFILE ON

PostgreSQL

EXPLAIN [ANALYZE] SELECT ...

MySQL

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

SQLite

EXPLAIN QUERY PLAN SELECT ...

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

SQL Server

BEGIN TRANSACTION;

PostgreSQL

BEGIN;

MySQL

START TRANSACTION;

SQLite

BEGIN;

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

SQL Server

[Order Date]

PostgreSQL

"Order Date"

MySQL

`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.

SQL Server

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

PostgreSQL

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

MySQL

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

SQLite

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

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