Portable SQL across database engines
What the standard actually guarantees, where PostgreSQL, MySQL, SQL Server and SQLite diverge, and the habits that keep a query movable.
Identifiers, quoting and case
The standard reserves double quotes for identifiers and single quotes for string literals. Most engines accept that, and then add their own quoting character — which is exactly the kind of detail that makes a query work on one engine and fail on the next.
-- standard: double quotes for identifiers, single quotes for strings
SELECT "order", 'it''s fine' FROM "sales"."orders";
-- quoting is not portable, so avoid needing it at all
SELECT "order" FROM sales.orders; -- PostgreSQL, standard
SELECT `order` FROM sales.orders; -- MySQL
SELECT [order] FROM sales.orders; -- SQL Server
-- lower_snake_case names that are not reserved words need no quoting anywhere
SELECT order_no, customer_id, placed_at FROM sales_orders;| Feature | PostgreSQL | MySQL | SQL Server | SQLite |
|---|---|---|---|---|
| Identifier quoting | "name" | backticks | [name] | "name" |
| Unquoted name case | Folded to lower case | As written, case-insensitive on Windows | As written | Case-insensitive |
| Auto-generated key | GENERATED ... AS IDENTITY | AUTO_INCREMENT | IDENTITY(1,1) | INTEGER PRIMARY KEY |
| String concatenation | || | CONCAT() | + or CONCAT() | || |
| Boolean literal | TRUE | TRUE (stored as 1) | 1 | 1 |
| Upsert | ON CONFLICT | ON DUPLICATE KEY UPDATE | MERGE | ON CONFLICT (3.24+) |
Row limits, concatenation and dates
-- limiting rows
SELECT * FROM products ORDER BY price DESC LIMIT 10; -- PostgreSQL, MySQL, SQLite
SELECT * FROM products ORDER BY price DESC FETCH FIRST 10 ROWS ONLY; -- standard, Oracle
SELECT TOP 10 * FROM products ORDER BY price DESC; -- SQL Server
-- paging, the two spellings you will meet
SELECT * FROM products ORDER BY price DESC LIMIT 10 OFFSET 20;
SELECT * FROM products ORDER BY price DESC OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
-- concatenating text: NULL behaviour differs
SELECT first_name || ' ' || last_name FROM people; -- PostgreSQL, SQLite
SELECT CONCAT(first_name, ' ', last_name) FROM people; -- MySQL, SQL Server: NULL becomes ''
SELECT first_name + ' ' + last_name FROM people; -- SQL Server: NULL spreads
-- current time
SELECT CURRENT_DATE, CURRENT_TIMESTAMP FROM t; -- standard
SELECT now(); -- PostgreSQL, MySQL, SQLite⚠️
CONCAT() in MySQL and SQL Server turns NULL into an empty string, while || in PostgreSQL returns NULL for the whole expression. Swapping one for the other during a migration changes your output for every row with a missing value, and nothing will error to tell you.Function equivalents
| Task | PostgreSQL | MySQL | SQL Server | SQLite |
|---|---|---|---|---|
| Current timestamp | now() | NOW() | GETDATE() | datetime('now') |
| Truncate to month | date_trunc('month', d) | DATE_FORMAT(d, '%Y-%m-01') | DATETRUNC(month, d) | strftime('%Y-%m-01', d) |
| Length of text | length(s) | CHAR_LENGTH(s) | LEN(s) | length(s) |
| Substring | substring(s from 1 for 3) | SUBSTRING(s,1,3) | SUBSTRING(s,1,3) | substr(s,1,3) |
| Null fallback | COALESCE | COALESCE / IFNULL | COALESCE / ISNULL | COALESCE / IFNULL |
| Regular expression | ~ and ~* | REGEXP | None built in | REGEXP (3.35+) |
| Random value | random() | RAND() | NEWID() | random() |
- Rely on the stable core:
CASE,COALESCE,CAST,CURRENT_TIMESTAMP,EXISTS, joins, window functions andFETCH FIRST. - Isolate engine-specific calls behind views or a thin data-access layer instead of scattering them through the codebase.
- Sort order and case sensitivity differ too: MySQL's default collation is case-insensitive, PostgreSQL's is case-sensitive, so an equality test can match different rows.
- Test on the engine you deploy to. Developing on SQLite and shipping to PostgreSQL is the most common source of last-minute surprises.
FAQ
Should I write portable SQL at all?
Write standard SQL by default — it is clearer and costs nothing. Accept dialect-specific syntax where it buys real performance or expressiveness, and keep those places few, documented and easy to find.
How do I know whether a query is portable?
Run it on every engine you support as part of your test suite. A small set of golden queries with expected results catches dialect drift long before it reaches production.
Related
Next steps: analytics, data modelling and practice Creating and altering schema with DDL
Last refreshed 2026-09-18.