Appendix J — SQL Dialect Differences

Roughly 90% of the SQL in this book is standard and runs on any major database. This appendix maps the differences for the other 10% — PostgreSQL vs. MySQL/MariaDB, SQL Server (T-SQL), Oracle, and SQLite. Concepts transfer; syntax varies.

Limiting rows

Need	PostgreSQL	MySQL	SQL Server	Oracle	SQLite
first N	`LIMIT n`	`LIMIT n`	`TOP n` / `OFFSET..FETCH`	`FETCH FIRST n ROWS ONLY`	`LIMIT n`
page	`LIMIT n OFFSET m`	`LIMIT m, n`	`OFFSET m ROWS FETCH NEXT n`	`OFFSET m ROWS FETCH NEXT n`	`LIMIT n OFFSET m`

(PostgreSQL also supports standard FETCH FIRST n ROWS ONLY.)

Auto-increment / identity

PostgreSQL	MySQL	SQL Server	Oracle	SQLite
`GENERATED ALWAYS AS IDENTITY` (or `serial`)	`AUTO_INCREMENT`	`IDENTITY(1,1)`	`GENERATED AS IDENTITY` (12c+)	`INTEGER PRIMARY KEY AUTOINCREMENT`

String concatenation

PostgreSQL	MySQL	SQL Server	Oracle	SQLite
`a \|\| b` (NULL-propagating); `concat()` ignores NULL	`CONCAT(a,b)` (`\|\|` is OR by default)	`a + b`; `CONCAT()`	`a \|\| b`; `CONCAT()`	`a \|\| b`

Case-insensitive matching

PostgreSQL	Others
`ILIKE`, or `~*` (regex)	`LOWER(col) LIKE LOWER(pattern)` (MySQL is often case-insensitive by collation; SQL Server by collation)

"Insert or update" (upsert)

PostgreSQL	MySQL	SQL Server / Oracle	SQLite
`INSERT ... ON CONFLICT (k) DO UPDATE` / `DO NOTHING`; `MERGE` (15+)	`INSERT ... ON DUPLICATE KEY UPDATE`	`MERGE`	`INSERT ... ON CONFLICT ... DO UPDATE`

Returning modified rows

PostgreSQL	SQL Server	Oracle	MySQL
`RETURNING col`	`OUTPUT inserted.col`	`RETURNING ... INTO`	`LAST_INSERT_ID()` (limited)

Data types (common mappings)

Concept	PostgreSQL	MySQL	SQL Server	Oracle
variable text	`text` / `varchar`	`VARCHAR`/`TEXT`	`varchar`/`nvarchar`	`VARCHAR2`/`CLOB`
exact decimal	`numeric`	`DECIMAL`	`decimal`	`NUMBER`
timestamp w/ tz	`timestamptz`	`TIMESTAMP`	`datetimeoffset`	`TIMESTAMP WITH TIME ZONE`
boolean	`boolean`	`TINYINT(1)`	`bit`	`NUMBER(1)` / no native bool
auto id	`integer ... identity`	`INT AUTO_INCREMENT`	`int IDENTITY`	`NUMBER ... identity`
JSON	`jsonb` (indexable)	`JSON`	`json`/`nvarchar`	`JSON`

Date / time functions (the least portable area)

Need	PostgreSQL	MySQL	SQL Server	Oracle
now	`now()` / `current_timestamp`	`NOW()`	`GETDATE()`	`SYSTIMESTAMP`
extract part	`extract(month from t)`	`MONTH(t)`	`DATEPART(month,t)`	`EXTRACT(MONTH FROM t)`
truncate to month	`date_trunc('month',t)`	`DATE_FORMAT(t,'%Y-%m-01')`	`DATETRUNC(month,t)` (2022+)	`TRUNC(t,'MM')`
add interval	`t + interval '1 day'`	`DATE_ADD(t, INTERVAL 1 DAY)`	`DATEADD(day,1,t)`	`t + 1` / `INTERVAL`
format	`to_char(t,'YYYY-MM-DD')`	`DATE_FORMAT()`	`FORMAT()`/`CONVERT()`	`TO_CHAR()`

Other notable differences

LIKE wildcards (%, _) are universal; regex differs (~ in PostgreSQL, REGEXP in MySQL, no portable standard).
GROUP BY strictness: PostgreSQL requires non-aggregated columns in GROUP BY (or PK-determined); MySQL historically allowed lax grouping (avoid relying on it).
ILIKE, DISTINCT ON, arrays, ranges, jsonb operators, GENERATED ... AS IDENTITY defaults, CREATE INDEX CONCURRENTLY, FILTER (WHERE) are PostgreSQL strengths; some are non-standard.
Sequences vs identity: all support auto-keys, with different syntax (above).
Quoting: standard identifiers use double quotes ("col"); MySQL also uses backticks; string literals use single quotes everywhere.
TRUE/FALSE: native in PostgreSQL; emulated (0/1) in some.

Portability guidance

Stick to standard SQL where you can (the bulk of this book).
Isolate dialect-specific bits (date functions, upsert, identity, RETURNING) behind your data-access layer (Ch. 29) so swapping databases is contained.
The concepts (joins, aggregation, windows, transactions, indexing, normalization) are universal — only the spellings change.

See also: callouts marked "Dialect Difference" throughout the chapters; Appendix C (quick reference), Appendix D (data types).