5 min read
SQL Dialects: PostgreSQL, MySQL, SQL Server, SQLite
Prerequisites: Common table expressions (WITH), Data types
The core of SQL — SELECT, WHERE, joins, GROUP BY, INSERT,
UPDATE, DELETE — works the same everywhere. The differences are in the
details. Here are the ones you'll hit most.
Common differences
| Task | PostgreSQL | MySQL | SQL Server | SQLite |
|---|---|---|---|---|
| Limit rows | LIMIT 10 |
LIMIT 10 |
TOP 10 / FETCH FIRST |
LIMIT 10 |
| Auto-increment ID | GENERATED ALWAYS AS IDENTITY |
AUTO_INCREMENT |
IDENTITY(1,1) |
INTEGER PRIMARY KEY |
| Join text | || or CONCAT |
CONCAT |
+ or CONCAT |
|| |
| Current date/time | NOW(), CURRENT_DATE |
NOW(), CURDATE() |
GETDATE() |
DATETIME('now') |
| Case-insensitive match | ILIKE |
LIKE (default collation) |
LIKE (default collation) |
LIKE (ASCII) |
| Upsert | ON CONFLICT |
ON DUPLICATE KEY UPDATE |
MERGE |
ON CONFLICT |
| Quote identifiers | "name" |
`name` |
[name] |
"name" |
| Boolean type | BOOLEAN |
TINYINT(1) alias |
BIT |
integer 0/1 |
Behaviour differences worth knowing
- Integer division —
7 / 2is3in PostgreSQL, SQL Server, and SQLite, but3.5000in MySQL. - Text comparisons — MySQL and SQL Server usually compare text case-insensitively by default; PostgreSQL and SQLite don't.
- Strictness — PostgreSQL rejects invalid dates and wrong types;
SQLite (without
STRICTtables) and some MySQL modes are more lenient. - GROUP BY — PostgreSQL requires every non-aggregated column to be
grouped; SQLite, and MySQL without
ONLY_FULL_GROUP_BY, allow looser queries that can return arbitrary values.
Choosing a database
- SQLite — embedded in apps and devices, tests, prototypes, and small websites. Zero setup; one writer at a time.
- PostgreSQL — the default choice for most new applications: strict, feature-rich (JSON, full-text search, extensions like PostGIS), and open source.
- MySQL / MariaDB — very widely deployed, especially in web hosting and PHP applications.
- SQL Server — common in companies built on Microsoft technology.
Learning standard SQL first, as this course does, makes switching between them straightforward: most of what changes is function names.