PostgreSQL vs MySQL — Key Syntax Differences, Side by Side

1. Identifier Quoting — Double Quotes vs Backticks

MySQL wraps table and column names in backticks: `order`. PostgreSQL wraps them in double quotes: "order". The characters are not interchangeable — a backtick is a hard syntax error in PostgreSQL, and a double-quoted token in MySQL is a string literal unless you enabled ANSI_QUOTES.

The reason both dialects have a quoting mechanism is that some identifiers collide with reserved words. You cannot write SELECT order FROM orders in either database, because order is part of ORDER BY. The fix is quoting:

-- MySQL
SELECT `order`.`select`, `order`.`group`
FROM `order`
WHERE `order`.`status` = 'paid';

-- PostgreSQL
SELECT "order"."select", "order"."group"
FROM "order"
WHERE "order"."status" = 'paid';

Single quotes are string literals in both dialects, so that part ports unchanged. What does not port is escaping inside strings. MySQL allows backslash escapes everywhere, so 'it\'s' is valid. PostgreSQL treats the backslash as an ordinary character unless the string is prefixed with E, which is why the standard form is to double the quote:

-- MySQL: backslash escape is on by default
SELECT 'it\'s fine';

-- PostgreSQL: E'' enables backslash escapes; otherwise double the quote
SELECT 'it''s fine';
SELECT E'it\'s fine';          -- equivalent, explicit escape string

PostgreSQL also has dollar quoting, which MySQL has no equivalent for. It exists so that function bodies full of single quotes do not turn into a wall of doubled apostrophes:

-- PostgreSQL only
CREATE FUNCTION greet(name text) RETURNS text AS $$
    SELECT 'hello, ' || name;
$$ LANGUAGE sql;

One more trap: in PostgreSQL, an unquoted identifier is folded to lowercase, but a quoted one is kept exactly as written. SELECT "UserId" FROM Users looks for a column literally named UserId and a table literally named Users — it will not find userid or users. MySQL is far looser (see section 8), so code migrated from MySQL often carries double quotes that were harmless there and become strict name-matching here.

2. String Concatenation — || vs CONCAT()

This is the difference that silently produces wrong results rather than an error message. In PostgreSQL || is the string concatenation operator, straight from the SQL standard. In MySQL, with default settings, || is the logical OR operator. So 'a' || 'b' returns ab in Postgres and 1 (meaning true) in MySQL.

-- MySQL
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM users;

-- PostgreSQL
SELECT first_name || ' ' || last_name AS full_name
FROM users;

MySQL can be made to treat || as concatenation by enabling the PIPES_AS_CONCAT SQL mode, but do not rely on a session setting to make ported code correct — a colleague reconnecting without it re-breaks the query. Write CONCAT() in MySQL and || in PostgreSQL.

Both databases offer CONCAT(), but they treat NULL differently, and that difference is another silent data bug. The || operator propagates NULL: any NULL operand makes the whole expression NULL. PostgreSQL's CONCAT() function, added in 9.1, skips NULLs instead, treating them as empty strings.

-- PostgreSQL: same data, two different answers
SELECT 'a' || NULL || 'b';        -- NULL   (operator propagates NULL)
SELECT CONCAT('a', NULL, 'b');    -- 'ab'   (function skips NULL)

-- Portable approach: decide explicitly and use COALESCE
SELECT COALESCE(first_name, '') || ' ' || COALESCE(last_name, '')
FROM users;

For joining a list of values where you want NULLs and empty strings both skipped, CONCAT_WS() (concatenate with separator) exists in both engines with the same semantics: it ignores NULL arguments and places the separator only between surviving values.

-- Works identically in MySQL and PostgreSQL
SELECT CONCAT_WS(', ', city, state, country) AS location FROM addresses;

3. Auto-Increment — SERIAL vs AUTO_INCREMENT

A surrogate primary key is where the DDL diverges first. MySQL attaches AUTO_INCREMENT to the column. PostgreSQL reaches for a sequence object, and SERIAL is shorthand that creates one behind your back.

-- MySQL
CREATE TABLE users (
    id    BIGINT NOT NULL AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

-- PostgreSQL: SERIAL is shorthand for a bigint column + sequence + default
CREATE TABLE users (
    id    BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL
);

-- PostgreSQL, modern form (10+): the SQL-standard identity column
CREATE TABLE users (
    id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email VARCHAR(255) NOT NULL
);

Three practical consequences of the difference:

• SERIAL is not a type. BIGSERIAL expands to bigint NOT NULL DEFAULT nextval('users_id_seq') plus sequence ownership. \d users in psql shows the column as plain bigint, which confuses people who go looking for a serial data type.
• Sequences are independent objects. They live in the same schema, survive a TRUNCATE of the table (unless you use TRUNCATE ... RESTART IDENTITY), and must be reset by hand after a bulk data load: SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));. MySQL's counter also survives a truncate, but it lives inside the table definition, so ALTER TABLE users AUTO_INCREMENT = 1; resets it in one statement.
• Identity columns reject manual inserts. GENERATED ALWAYS AS IDENTITY throws an error if you supply the value, which is a feature — it catches accidental id hard-coding. Use GENERATED BY DEFAULT AS IDENTITY if you need to load explicit ids in tests.

Reading back the generated key differs too, and this one bites every ORM developer during a migration:

-- MySQL: insert, then ask the session for the last generated value
INSERT INTO users (email) VALUES ('a@example.com');
SELECT LAST_INSERT_ID();

-- PostgreSQL: the INSERT itself returns the value
INSERT INTO users (email) VALUES ('a@example.com')
RETURNING id;

RETURNING works on INSERT, UPDATE, and DELETE in PostgreSQL, which makes the read-modify-write round trip a single statement. MySQL has no RETURNING clause — it is the single most requested MySQL feature for good reason.

4. Booleans — TRUE vs TINYINT(1)

PostgreSQL has a real boolean type with three states: true, false, and NULL. MySQL has no boolean type at all — BOOL and BOOLEAN are aliases for TINYINT(1), a one-byte integer where 0 means false and anything nonzero means true. TRUE and FALSE are just synonyms for 1 and 0.

-- MySQL: the column is an integer wearing a boolean costume
CREATE TABLE flags (id INT PRIMARY KEY, is_active BOOLEAN DEFAULT TRUE);
INSERT INTO flags VALUES (1, TRUE);
SELECT is_active, is_active = 1, is_active + 10 FROM flags;   -- 1, 1, 11

-- PostgreSQL: a genuine boolean
CREATE TABLE flags (id INT PRIMARY KEY, is_active BOOLEAN DEFAULT TRUE);
INSERT INTO flags VALUES (1, TRUE);
SELECT is_active, is_active = TRUE, NOT is_active FROM flags; -- t, t, f

The consequences are more than cosmetic. In MySQL, SELECT is_active FROM flags returns 1, and a client library hands you an integer — which is why so much PHP and Java code tests == 1 instead of == true. Port that to PostgreSQL and the comparison against 1 fails: WHERE is_active = 1 raises operator does not exist: boolean = integer. Write WHERE is_active = TRUE or simply WHERE is_active.

Literal handling differs too. PostgreSQL accepts 't', 'true', 'yes', 'on', and '1' when casting a string to boolean, which makes data loads forgiving. MySQL is comparably lenient because it coerces to an integer, but the comparison result is a number, not a truth value. And a PostgreSQL boolean can be NULL, so a tri-state column (yes / no / unknown) is natural there; in MySQL you must remember that 0 and NULL are different things and write IS NOT TRUE rather than != 1 when the column is nullable.

5. Date and Time Functions

Date arithmetic is where ports fail loudly, because the function names are similar but not the same. Both engines share NOW(), CURRENT_TIMESTAMP, CURDATE()/CURRENT_DATE, and EXTRACT(), and then diverge immediately after.

Two things stand out. First, the operators: PostgreSQL lets you add an interval to a date or timestamp with a plain +, and adding an integer to a date adds that many days — CURRENT_DATE + 1 is tomorrow. MySQL requires the function form or d + INTERVAL 1 DAY. Second, the format strings are entirely different languages: MySQL uses DATE_FORMAT specifiers like %Y-%m-%d %H:%i:%s, PostgreSQL uses TO_CHAR patterns like YYYY-MM-DD HH24:MI:SS. Translating a format string is the most error-prone part of a port, because both languages accept many of the same letters with different meaning.

-- Group orders into monthly buckets

-- MySQL
SELECT DATE_FORMAT(created_at, '%Y-%m-01') AS month,
       COUNT(*) AS orders
FROM orders
GROUP BY month
ORDER BY month;

-- PostgreSQL
SELECT DATE_TRUNC('month', created_at) AS month,
       COUNT(*) AS orders
FROM orders
GROUP BY month
ORDER BY month;

Time zones add a final divergence. PostgreSQL has two distinct types — TIMESTAMP WITHOUT TIME ZONE and TIMESTAMP WITH TIME ZONE (timestamptz) — and NOW() returns the latter, stored as UTC and rendered in the session time zone. MySQL stores DATETIME with no zone information at all and TIMESTAMP as UTC-with-conversion, with a narrower range (1970–2038). If your application sends local wall-clock times, the same row means different instants after the move; store UTC explicitly and convert in the application or with AT TIME ZONE.

6. UPSERT — ON CONFLICT vs ON DUPLICATE KEY UPDATE

Both databases can insert a row or update it if the key already exists, in a single statement. The syntax shares nothing beyond the words INSERT and UPDATE.

-- MySQL
INSERT INTO counters (name, value)
VALUES ('page_views', 1)
ON DUPLICATE KEY UPDATE value = value + 1;

-- PostgreSQL
INSERT INTO counters (name, value)
VALUES ('page_views', 1)
ON CONFLICT (name) DO UPDATE SET value = counters.value + 1;

The structural difference matters. MySQL's clause fires on any unique key violation, and you cannot tell it which key you expected to conflict — the server picks whichever index happens to be violated. PostgreSQL's ON CONFLICT names the conflict target explicitly: ON CONFLICT (name) listens only to the unique index on name. If a different constraint fires, you get an honest error instead of an update happening on a row you did not mean to touch. Porting MySQL upserts to PostgreSQL is therefore not mechanical: for each statement you must know which column or columns carry the unique constraint.

Reading the existing row's values differs too. PostgreSQL exposes the proposed new row as the pseudo-table EXCLUDED, while MySQL has historically used the VALUES() function, which was deprecated in MySQL 8.0.20 in favor of an aliased row.

-- PostgreSQL: EXCLUDED is the row you tried to insert
INSERT INTO products (sku, price, updated_at)
VALUES ('ABC-1', 19.99, NOW())
ON CONFLICT (sku) DO UPDATE
SET price      = EXCLUDED.price,
    updated_at = EXCLUDED.updated_at;

-- MySQL, classic form (VALUES() is deprecated in 8.0.20+)
INSERT INTO products (sku, price, updated_at)
VALUES ('ABC-1', 19.99, NOW())
ON DUPLICATE KEY UPDATE
    price      = VALUES(price),
    updated_at = VALUES(updated_at);

-- MySQL, modern alias form (8.0.19+)
INSERT INTO products (sku, price, updated_at)
VALUES ('ABC-1', 19.99, NOW()) AS new
ON DUPLICATE KEY UPDATE
    price      = new.price,
    updated_at = new.updated_at;

For "insert only if absent, do nothing otherwise", the dialects are:

-- MySQL
INSERT IGNORE INTO tags (name) VALUES ('sql');

-- PostgreSQL
INSERT INTO tags (name) VALUES ('sql') ON CONFLICT DO NOTHING;

Beware INSERT IGNORE: it downgrades every error to a warning, not just duplicate-key collisions, so a truncated value or a failed type coercion is silently swallowed. PostgreSQL's ON CONFLICT DO NOTHING suppresses only the unique-violation case, which is what you actually wanted. REPLACE INTO is not a substitute either — it deletes the conflicting row and inserts a new one, which resets the auto-increment id, fires ON DELETE triggers, and drops any column you did not list.

7. Pagination — LIMIT and OFFSET Details

Both engines accept LIMIT, which is a MySQL extension that PostgreSQL adopted, but the offset forms are not the same set.

-- First page: identical in both
SELECT id, title FROM posts ORDER BY created_at DESC LIMIT 10;

-- Offset pagination

-- MySQL: both of these mean "skip 20, take 10"
SELECT id, title FROM posts ORDER BY created_at DESC LIMIT 10 OFFSET 20;
SELECT id, title FROM posts ORDER BY created_at DESC LIMIT 20, 10;

-- PostgreSQL: the comma form is a syntax error
SELECT id, title FROM posts ORDER BY created_at DESC LIMIT 10 OFFSET 20;

-- PostgreSQL also understands the SQL:2008 form (MySQL 8 does not)
SELECT id, title FROM posts ORDER BY created_at DESC
OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY;

So the one habit to break is LIMIT offset, count. It is the compact form every MySQL developer writes and the one that throws a syntax error the moment it reaches PostgreSQL. Note also that ORDER BY is not optional in practice even though both engines allow it — without it, which ten rows you get is undefined, and a query that appears to work in testing will shuffle in production.

At large offsets both dialects have the same performance problem: OFFSET 1000000 makes the server read and discard a million rows before returning anything. The fix is keyset pagination in both engines — page by the last seen value instead of by counting rows.

-- Keyset pagination: fast in both, no OFFSET scan

-- MySQL
SELECT id, title FROM posts
WHERE created_at < '2026-09-01 12:00:00'
ORDER BY created_at DESC
LIMIT 10;

-- PostgreSQL
SELECT id, title FROM posts
WHERE created_at < TIMESTAMP '2026-09-01 12:00:00'
ORDER BY created_at DESC
LIMIT 10;

PostgreSQL additionally supports LIMIT ALL (remove the limit) and can apply LIMIT to a WITH subquery that MySQL constrains differently, but the practical set is above: use LIMIT n OFFSET m in both, never use the comma form, and switch to keyset pagination before your offset crosses five figures.

8. Case Sensitivity of Identifiers and Text

Keywords and function names are case-insensitive in both databases — select, SELECT, and SeLeCt are the same token. Everything else is where they part ways, and the divergence has two independent halves.

Identifiers

PostgreSQL lowercases unquoted identifiers, so Users, users, and USERS all resolve to the same table — as long as you never quote them. Quote one and it becomes exact-match and case-sensitive: "Users" and "users" are two different tables that can coexist.

MySQL's behavior depends on the storage layer. Column names are case-insensitive on every platform. Table names follow lower_case_table_names: on Linux the default is 0, meaning table names are case-sensitive on a case-sensitive filesystem; on Windows and macOS the default is 1, meaning names are folded to lowercase and therefore case-insensitive. This is why a schema that runs on a developer's Mac fails to deploy on a Linux CI box, and it is the single most common "works on my machine" incident after a MySQL-to-Linux move.

Text comparison — the bigger surprise

Here the defaults are opposite, and this is the difference most likely to ship a wrong answer rather than an error:

-- MySQL: default collation utf8mb4_0900_ai_ci is case-insensitive
SELECT id, email FROM users WHERE email LIKE 'alice%';   -- matches 'Alice@...'

-- PostgreSQL: default collation is case-sensitive
SELECT id, email FROM users WHERE email LIKE 'alice%';   -- does NOT match 'Alice@...'

-- PostgreSQL fixes: ILIKE, or normalize both sides
SELECT id, email FROM users WHERE email ILIKE 'alice%';
SELECT id, email FROM users WHERE LOWER(email) LIKE 'alice%';

PostgreSQL has ILIKE for exactly this purpose, and it also supports the ~* operator for case-insensitive regular matching. MySQL's case-insensitivity is a property of the column's collation — you can force a case-sensitive comparison with the BINARY keyword or a _bin collation, but the default is the forgiving one. Moving to PostgreSQL therefore tightens matching: queries that returned extra rows now return fewer, and users notice missing results before they notice errors.

Quick checklist for a port

• Reject any query containing backticks; convert to double quotes only where the identifier is genuinely mixed-case or reserved.
• Rewrite || used as OR into OR, and CONCAT() chains into || or COALESCE.
• Replace AUTO_INCREMENT with GENERATED ... AS IDENTITY and add RETURNING where the application read LAST_INSERT_ID().
• Convert BOOL columns to BOOLEAN and audit every comparison against 0 or 1.
• Translate DATE_FORMAT specifiers to TO_CHAR patterns by hand, character group by character group.
• Split every ON DUPLICATE KEY UPDATE into ON CONFLICT (columns) DO UPDATE, naming the real unique key.
• Search for LIMIT n, m and LIKE patterns; both change meaning or break outright.

FAQ — PostgreSQL vs MySQL Syntax