Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.
The MySQL vs PostgreSQL syntax differences that break migrations: auto-increment, quoting, string concat, upserts, and NULL functions — with side-by-side SQL.
Quick answer
The main MySQL vs PostgreSQL syntax differences are: auto-increment (AUTO_INCREMENT vs GENERATED AS IDENTITY/SERIAL), identifier quoting (backticks vs double quotes), string concatenation (CONCAT() vs the || operator), and upserts (ON DUPLICATE KEY UPDATE vs ON CONFLICT). PostgreSQL follows the SQL standard more closely; MySQL has more permissive, non-standard shortcuts.
FAQs
Is PostgreSQL syntax compatible with MySQL?
Mostly, but not fully. Standard SELECT, JOIN, WHERE, GROUP BY, and common data types are the same. The breaking differences are auto-increment columns, identifier quoting, string concatenation, upsert syntax, and some function names. A query using only standard SQL usually ports cleanly; one using dialect shortcuts needs edits.
Why does || not work for string concatenation in MySQL?
By default MySQL treats || as the logical OR operator, so 'a' || 'b' evaluates as a boolean expression rather than concatenating. PostgreSQL follows the SQL standard where || concatenates strings. Use CONCAT() in MySQL, or enable the PIPES_AS_CONCAT SQL mode to make || concatenate.
What replaces AUTO_INCREMENT in PostgreSQL?
Use GENERATED ALWAYS AS IDENTITY, the SQL-standard syntax supported since PostgreSQL 10 and the recommended choice. The older SERIAL pseudo-type also works and creates an underlying sequence. Both give you an auto-incrementing integer primary key equivalent to MySQL's AUTO_INCREMENT.
How do I convert a MySQL upsert to PostgreSQL?
Replace INSERT ... ON DUPLICATE KEY UPDATE with INSERT ... ON CONFLICT (column) DO UPDATE SET. Where MySQL references the incoming row with VALUES(col), PostgreSQL uses EXCLUDED.col. You must name the conflicting unique or primary-key column in the ON CONFLICT clause, which MySQL infers automatically.
Try it now
Paste your SQL into sqlfmt — format, validate, and catch typos free in your browser.
MySQL and PostgreSQL agree on the bulk of SQL — SELECT, JOIN, GROUP BY, and the core data types are nearly identical. The MySQL vs PostgreSQL syntax differences that actually break a migration cluster in a handful of places: auto-increment columns, identifier quoting, string concatenation, upserts, and a few function names.
The throughline: PostgreSQL hews closer to the SQL standard, while MySQL offers permissive, non-standard shortcuts. Knowing exactly where they diverge saves hours when porting a query in either direction. Here's each difference with side-by-side SQL.
Auto-increment: AUTO_INCREMENT vs IDENTITY
This is the first wall you hit. MySQL marks a self-incrementing primary key with AUTO_INCREMENT. PostgreSQL has no such keyword — use the standard GENERATED ALWAYS AS IDENTITY (preferred since Postgres 10) or the older SERIAL pseudo-type.
MySQL
CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL);
PostgreSQL
CREATE TABLE users ( id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR(100) NOT NULL);
Quoting identifiers: backticks vs double quotes
MySQL quotes identifiers — column and table names — with backticks. PostgreSQL uses double quotes, per the standard. And critically, MySQL treats double quotes as string literals by default, so a Postgres query that double-quotes a column will misbehave in MySQL and vice versa. We cover this fully in our guide to single vs double quotes in SQL.
MySQL uses backticks; PostgreSQL uses double quotes
-- MySQLSELECT `order`, `user name` FROM `orders`;-- PostgreSQLSELECT "order", "user name" FROM "orders";
String concatenation: CONCAT() vs ||
In PostgreSQL, the standard string concatenation operator is ||. In MySQL, || means logical OR by default — so a || b returns a boolean, not a joined string. MySQL's portable concatenation is CONCAT(), which also works in PostgreSQL, making CONCAT() the safest choice for cross-dialect code.
PostgreSQL: || concatenates; MySQL: || is OR
-- PostgreSQL: returns 'Ada Lovelace'SELECT first_name || ' ' || last_name AS full_name FROM people;-- MySQL (and portable everywhere): use CONCATSELECT CONCAT(first_name, ' ', last_name) AS full_name FROM people;
Upserts: ON DUPLICATE KEY UPDATE vs ON CONFLICT
"Insert, or update if it already exists" has totally different syntax. MySQL uses ON DUPLICATE KEY UPDATE and references the incoming row with VALUES() (or the newer alias syntax). PostgreSQL uses the standard-ish ON CONFLICT, naming the conflicting column and referencing the incoming row via the EXCLUDED pseudo-table.
INSERT INTO inventory (sku, qty)VALUES ('A100', 5)ON CONFLICT (sku) DO UPDATE SET qty = inventory.qty + EXCLUDED.qty;
Functions and types that differ
A grab-bag of smaller divergences that bite during migration:
NULL fallback: MySQL has IFNULL(x, y); PostgreSQL doesn't. Both support the standard COALESCE(x, y) — prefer it.
Current time: both have NOW(); MySQL also has CURDATE()/CURTIME(), Postgres uses CURRENT_DATE/CURRENT_TIME.
Booleans: PostgreSQL has a real BOOLEAN type with TRUE/FALSE; MySQL stores BOOLEAN as TINYINT(1) and accepts 1/0.
String case: PostgreSQL comparisons are case-sensitive by default; MySQL's default collation is case-insensitive, so WHERE name = 'ada' matches 'Ada'.
LIMIT: both support LIMIT n OFFSET m; PostgreSQL also accepts the standard FETCH FIRST n ROWS ONLY.
Checklist: porting a query between MySQL and PostgreSQL
Swap AUTO_INCREMENT for GENERATED ALWAYS AS IDENTITY (or back).
Convert backtick identifiers to double quotes — or drop the quotes entirely if the names are plain.
Replace || string concatenation with CONCAT() when targeting MySQL.
Rewrite ON DUPLICATE KEY UPDATE as ON CONFLICT ... DO UPDATE (and VALUES() as EXCLUDED).
Change IFNULL to COALESCE and double-check date/time function names.
Verify string comparisons — case sensitivity flips between the two defaults.
Run the result through a dialect-aware validator before shipping.
Wrapping up
Most MySQL vs PostgreSQL differences come down to MySQL's non-standard shortcuts versus PostgreSQL's standards-first design — auto-increment, quoting, concatenation, and upserts are where you'll spend your porting time. sqlfmt parses and validates against both MySQL and PostgreSQL grammars, so when you switch the dialect it flags the constructs that won't survive the move — before the database does.