SQL / SECURITY, ROUTINES, AND DIALECTS
MySQL, PostgreSQL, SQLite, and SQL Server compared
Read and write the same query across MySQL, PostgreSQL, SQLite and SQL Server, and know which parts break when you change engine.
What you will learn
- Rewrite LIMIT as TOP or OFFSET/FETCH when moving a paged query to SQL Server
- Predict whether '42' or 'warm' survives an INTEGER column or raises an error
- Choose the right identifier quoting per engine and know when = ignores case
- Isolate dialect-specific SQL so the portable core stays shared across engines
Understanding MySQL, PostgreSQL, SQLite, and SQL Server compared
All four engines run SELECT, FROM, WHERE, JOIN, GROUP BY, HAVING, CASE, COALESCE and subqueries the same way, and current versions agree on common table expressions and window functions too. What diverges is everything wrapped around that core, because the standard either left it open or the vendors got there first: how you limit rows, how you generate a primary key, how you join two strings, what happens to a value that does not fit the column type, and how you quote a name that collides with a keyword. The useful mental model is one portable language in the middle and four short translation tables at the edges.
The architectural differences explain the syntactic ones. SQLite is a library running inside your process against a single file, so it has no accounts and no stored procedures, and its declared types are advisory hints called affinity: INTEGER means "convert this if you can", which is why the text 'warm' can sit in an INTEGER column. PostgreSQL is the strictest and most standard-leaning: text comparison is case-sensitive, there is a real boolean type, unquoted identifiers fold to lower case, and a value that does not parse raises an error instead of being coerced. MySQL leans on collation rather than case, so with a default *_ci collation `WHERE email = 'A@B.com'` matches 'a@b.com', and it quotes identifiers with backticks; SQL Server speaks T-SQL, with TOP instead of LIMIT, IDENTITY(1,1) instead of AUTO_INCREMENT, + instead of ||, and [brackets] around names.
In practice, decide which engines a statement must run on before you write it, keep the shared work in the portable core, and push each edge case behind one boundary per engine. Chasing a lowest-common-denominator subset usually costs more than it saves, since you give up upsert, RETURNING and window functions to avoid writing four short variants of one INSERT. Test against the real engine rather than a stand-in: developing on SQLite and deploying to PostgreSQL hides precisely the differences that matter, because SQLite accepts constructs the server rejects.
CREATE TABLE reading (id INTEGER PRIMARY KEY, celsius INTEGER);
INSERT INTO reading (id, celsius) VALUES (1, 21), (2, '22'), (3, 'warm');
SELECT id, celsius, typeof(celsius) AS stored_type FROM reading ORDER BY id;SQL is a small portable core surrounded by four dialects, and the differences that bite you all live at the edges: row limiting, generated keys, string operators, type strictness and quoting.
Worked examples
Limiting rows
The same top-N query spelled the way three engines accept and SQL Server does not.
CREATE TABLE score (player TEXT, points INTEGER);
INSERT INTO score (player, points) VALUES ('ada', 90), ('lin', 75), ('bo', 61);
SELECT player, points FROM score ORDER BY points DESC LIMIT 2;Example explained
Line 1LIMIT 2 after ORDER BY works unchanged on MySQL, PostgreSQL and SQLite.
Line 2SQL Server has no LIMIT: write SELECT TOP (2) before the column list, or ORDER BY points DESC OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY, which PostgreSQL also accepts.
Line 3TOP moves the clause to a different position in the statement, so this is a rewrite rather than a keyword swap.
Line 4Without ORDER BY, all four engines may return any two rows, so a portable top-N always needs an ordering.
Joining strings
Shows why || and + are not interchangeable, and why the wrong one can fail silently.
SELECT 'Ada' || ' ' || 'Lovelace' AS piped,
'Ada' + ' ' + 'Lovelace' AS plussed;Example explained
Line 1|| is the standard concatenation operator and behaves this way in PostgreSQL, SQLite and Oracle.
Line 2+ is SQL Server's concatenation operator, but SQLite treats + as arithmetic and casts each non-numeric string to 0, so plussed is 0 rather than an error.
Line 3On MySQL with default sql_mode, || is logical OR, so the first expression returns 0 instead of a name.
Line 4CONCAT('Ada', ' ', 'Lovelace') is the one spelling MySQL, PostgreSQL and SQL Server 2012+ all accept.
Generated keys
Four engines, four ways to say "fill this primary key for me".
CREATE TABLE note (id INTEGER PRIMARY KEY, body TEXT NOT NULL);
INSERT INTO note (body) VALUES ('first'), ('second');
SELECT id, body FROM note ORDER BY id;Example explained
Line 1In SQLite, exactly INTEGER PRIMARY KEY aliases the hidden rowid and is auto-filled; writing INT PRIMARY KEY instead gives an ordinary column that stays NULL.
Line 2The equivalents are id INT AUTO_INCREMENT on MySQL, id integer GENERATED ALWAYS AS IDENTITY on PostgreSQL, and id int IDENTITY(1,1) on SQL Server.
Line 3Reading the new key back also differs: last_insert_rowid(), LAST_INSERT_ID(), RETURNING id, and SCOPE_IDENTITY().
Line 4Because the spellings differ in the DDL, one schema file cannot serve all four engines without generation or per-engine variants.
Insert-or-update
Upsert is where the dialects diverge most, including a total absence in SQL Server.
CREATE TABLE stock (sku TEXT PRIMARY KEY, qty INTEGER NOT NULL);
INSERT INTO stock (sku, qty) VALUES ('A1', 5);
INSERT INTO stock (sku, qty) VALUES ('A1', 3)
ON CONFLICT (sku) DO UPDATE SET qty = qty + excluded.qty;
SELECT sku, qty FROM stock;Example explained
Line 1ON CONFLICT (sku) names the unique index that decides whether this is an insert or an update; PostgreSQL introduced this form and SQLite adopted it in 3.24.
Line 2Inside DO UPDATE, the unqualified qty is the stored value and excluded.qty is the value that was offered, so 5 + 3 gives 8.
Line 3MySQL writes ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty), infers the key itself, and has no excluded pseudo-table.
Line 4SQL Server has no clause of this shape: you use MERGE, or an UPDATE followed by a conditional INSERT inside a transaction.
Important notes
Version matters as much as engine: OFFSET/FETCH arrived in SQL Server 2012, upsert in SQLite 3.24, STRICT tables (which do enforce declared types) in SQLite 3.37, and window functions in MySQL 8.0.
SQLite has no user accounts, no GRANT, and no stored procedures — access control is file permissions — so any design that relies on database roles or server-side routines does not port to it.
Common mistakes
Quoting a string literal with double quotes because SQLite tolerated it: SQLite falls back to text when no such column exists, while PostgreSQL and SQL Server always read "warm" as an identifier, so the query fails with a missing-column error the first time it runs in production.
Using || to concatenate on MySQL: with default sql_mode it is logical OR, so first_name || last_name evaluates to 0 and you ship a column of zeros instead of getting an error.
Assuming = ignores case because MySQL's default collation does: after a move to PostgreSQL a lookup on Ada@site.com no longer matches ada@site.com and the unique index no longer collapses case variants, so logins break and duplicate accounts appear.
Try it yourself
Change, predict, then run
In a SQLite browser editor, create a table with one INTEGER column, insert 7, '8' and 'eight', then run SELECT with typeof() to see which values kept the declared type. Next to each row, note whether MySQL in strict mode, PostgreSQL and SQL Server would have accepted that INSERT.
Open the SQL workspaceCheck your understanding
A query that runs correctly against your local SQLite database fails on PostgreSQL with: column "warm" does not exist. What is the most likely cause?
- PostgreSQL requires every string literal to be cast to text before comparison.
- SQLite stored the value as text, so PostgreSQL cannot compare it to the column.
- The query wraps a string literal in double quotes; SQLite falls back to treating it as text, while PostgreSQL always reads double quotes as an identifier.
- PostgreSQL folds table names to lower case, so the table that owns the column was not found.
Show answer
Double quotes delimit identifiers in standard SQL; SQLite keeps a compatibility misfeature where a double-quoted name that matches no column is reused as a string literal, so 'warm' worked locally and became a column reference on PostgreSQL. Option 3 is tempting because PostgreSQL really does fold unquoted names to lower case, but that produces an error naming the table or column you actually wrote, not one naming a data value.