MySQL, PostgreSQL and SQL Server share more than most people expect and differ in more than they hope. The core of the language — SELECT, JOIN, GROUP BY, window functions, CTEs — is genuinely standard and behaves the same in PostgreSQL, MySQL and SQL Server. What differs is the dialect around the edges: how you quote an identifier, how you limit rows, how you spell an upsert, and which date function extracts a month. Learn the core once and the edges by lookup, and a query will move between engines with a handful of edits rather than a rewrite.
What differs between MySQL, PostgreSQL and SQL Server?
This lesson compares the three engines side by side. Nothing here is executed in a single engine, because the point is that the same idea has three spellings — and the spelling a feature needs is the thing to look up, not something to memorise in advance.
How do the engines quote identifiers?
MySQL uses backticks, and SQL Server uses square brackets; both accept them around a reserved word such as order or user. PostgreSQL prefers double quotes, and — this is the part that catches people — double quotes in PostgreSQL mean "a case-sensitive identifier", while single quotes mean "a string literal". MySQL by default treats double quotes as a string literal, so a query written with PostgreSQL quoting silently produces a string where PostgreSQL expects a name.
PostgreSQL spelling, quoting an identifier that is also a keyword:
SELECT "order" FROM "order_line" WHERE "user" = 'ada';
MySQL spelling, the same idea:
SELECT `order` FROM `order_line` WHERE `user` = 'ada';
SQL Server spelling, the same idea again:
SELECT [order] FROM [order_line] WHERE [user] = 'ada';
The portable advice is the same as it has been since module 02: do not use reserved words for column names, and you never have to quote anything.
How do the engines join strings?
The || operator is standard SQL, and PostgreSQL and SQLite implement it. MySQL, unless the PIPES_AS_CONCAT server mode is enabled, treats || as a logical OR — a genuinely dangerous difference, because the query runs and returns the wrong rows instead of failing. SQL Server uses + for strings and + for numbers alike, which means a concatenation with a NULL silently produces NULL rather than an error.
PostgreSQL spelling, using the standard operator or the function:
SELECT first_name || ' ' || last_name AS full_name FROM customers;
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;
MySQL spelling, where CONCAT is the only safe choice:
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;
SELECT CONCAT_WS(' ', first_name, last_name) AS full_name FROM customers;
SQL Server spelling, with CONCAT as the NULL-safe option:
SELECT first_name + ' ' + last_name AS full_name FROM customers;
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;
MySQL's CONCAT_WS and PostgreSQL's concat_ws are close relatives that skip NULL arguments, which makes them the tidiest way to build a label from optional parts. SQL Server has no direct equivalent and uses COALESCE or CONCAT_WS from SQL Server 2017 onwards.
How do the engines limit rows?
LIMIT is the standard and works in PostgreSQL and MySQL. SQL Server has TOP, placed before the select list, and also supports the standard FETCH FIRST since version 2012. Offset paging is LIMIT n OFFSET m in PostgreSQL and MySQL, and OFFSET m ROWS FETCH NEXT n ROWS ONLY in SQL Server.
PostgreSQL and MySQL spelling:
SELECT id FROM sales ORDER BY id LIMIT 10 OFFSET 20;
SQL Server spelling, with TOP:
SELECT TOP 10 id FROM sales ORDER BY id OFFSET 20 ROWS;
SQL Server spelling, with the standard form:
SELECT id FROM sales ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
One portability warning that belongs here: LIMIT without ORDER BY returns an arbitrary subset. If the same query must return the same rows every time — in a pagination loop, or in a test — the ordering has to be explicit and the ordering key has to be unique.
How does each engine upsert?
The upsert of module 35 has three spellings. PostgreSQL and modern SQLite use ON CONFLICT. MySQL uses ON DUPLICATE KEY UPDATE, which is triggered by any unique key rather than by a named column. SQL Server and Oracle use the full MERGE statement, and PostgreSQL added MERGE in version 15.
PostgreSQL and SQLite spelling:
INSERT INTO customers (customer_id, full_name) VALUES (1, 'Acme Ltd')
ON CONFLICT (customer_id) DO UPDATE SET full_name = EXCLUDED.full_name;
MySQL spelling:
INSERT INTO customers (customer_id, full_name) VALUES (1, 'Acme Ltd')
ON DUPLICATE KEY UPDATE full_name = VALUES(full_name);
SQL Server spelling, with MERGE:
MERGE INTO customers AS target
USING (SELECT 1 AS customer_id, 'Acme Ltd' AS full_name) AS source
ON target.customer_id = source.customer_id
WHEN MATCHED THEN UPDATE SET target.full_name = source.full_name
WHEN NOT MATCHED THEN INSERT (customer_id, full_name) VALUES (source.customer_id, source.full_name);
MySQL's VALUES(full_name) is deprecated in favour of a row alias in MySQL 8.0.19 and later, and PostgreSQL's EXCLUDED is the equivalent idea under a different name. MySQL also fires BEFORE INSERT triggers on an upsert where PostgreSQL's ON CONFLICT does not — the sort of difference that makes cross-engine testing worthwhile rather than optional.
What about types and dates?
Dates are the biggest source of cross-engine surprise. PostgreSQL has a real DATE type and a family of timestamp types, MySQL has DATE, DATETIME and TIMESTAMP with its own zero-date rules, and SQL Server has DATE, DATETIME2 and a legacy DATETIME with a millisecond rounding. Extracting a month therefore has three spellings:
PostgreSQL spelling:
SELECT EXTRACT(MONTH FROM sale_date) AS month FROM sales;
SELECT to_char(sale_date, 'YYYY-MM') AS month_key FROM sales;
MySQL spelling:
SELECT MONTH(sale_date) AS month FROM sales;
SELECT DATE_FORMAT(sale_date, '%Y-%m') AS month_key FROM sales;
SQL Server spelling:
SELECT DATEPART(month, sale_date) AS month FROM sales;
SELECT FORMAT(sale_date, 'yyyy-MM') AS month_key FROM sales;
None of these three is the standard EXTRACT that the SQL standard specifies, which is one of the few places where the standard and the market diverge completely. MySQL's format strings use percent signs and its STR_TO_DATE is picky about order; SQL Server's FORMAT is slow on large result sets and its CONVERT with a style code is usually the better tool. The portable fallback is the one from module 03: store dates as ISO text and extract with SUBSTR.
How do booleans and generated identifiers differ?
PostgreSQL has a true boolean type. MySQL and SQL Server do not — MySQL stores a boolean as a tinyint with 0 and 1, and SQL Server as a bit. SQLite, as module 45 showed, stores it as an integer as well. A query written with WHERE completed = TRUE works in PostgreSQL and fails in MySQL.
PostgreSQL spelling, with a real boolean and an identity column:
CREATE TABLE flags (id INTEGER GENERATED ALWAYS AS IDENTITY, is_done BOOLEAN NOT NULL DEFAULT FALSE);
MySQL spelling:
CREATE TABLE flags (id INT AUTO_INCREMENT PRIMARY KEY, is_done TINYINT(1) NOT NULL DEFAULT 0);
SQL Server spelling:
CREATE TABLE flags (id INT IDENTITY(1,1) PRIMARY KEY, is_done BIT NOT NULL DEFAULT 0);
The same three-way split applies to generated identifiers, and module 46 covered it in detail: an integer identity column in all three, with the exact syntax above.
What is genuinely portable?
A long list, and it covers most of what this course has taught. The SELECT list, aliases and expressions. All the join types, including the outer joins and the CROSS JOIN. WHERE, GROUP BY, HAVING and the aggregation functions. The CASE expression. Window functions with PARTITION BY, ORDER BY and the whole ranking, framing, LAG and LEAD family — all three engines have supported these since 2012. Common table expressions, including recursive ones. INSERT, UPDATE, DELETE, and the basic CREATE TABLE syntax with the portable type names: INTEGER, REAL, TEXT, VARCHAR(n), DATE and TIMESTAMP. And the transaction statements BEGIN, COMMIT and ROLLBACK, with savepoints in all three.
What is not portable is the edge: identifier quoting, string concatenation, row limiting, upserts, date functions, boolean literals, generated identifiers, and the proprietary extras each engine adds — PostgreSQL's arrays and JSONB, MySQL's GROUP_CONCAT, SQL Server's APPLY and PIVOT. Learn where the edges are and you can write a query that runs on all three, or a query that deliberately uses one engine's edge and says so.
Quick reference
- The core is standard: joins, aggregates, CASE, window functions, CTEs and transactions all port.
- Identifiers: MySQL uses backticks, PostgreSQL double quotes, SQL Server square brackets.
- Concatenation: || is standard but MySQL needs CONCAT; CONCAT_WS is the tidiest option.
- Row limits: LIMIT everywhere except SQL Server's TOP or FETCH FIRST.
- Upserts: ON CONFLICT, ON DUPLICATE KEY UPDATE and MERGE respectively.
- Dates: EXTRACT, DATE_FORMAT and DATEPART are three spellings of one idea — or store ISO text and use SUBSTR.
Portability drill: write the same query three times — once per dialect — to list the top three customers by revenue, and then mark every difference you had to make. That list is your portability budget, and it is usually shorter than people expect.