PostgreSQL vs. MySQL: Syntax Differences Compared

1. Why PostgreSQL and MySQL differ despite a shared foundation

PostgreSQL and MySQL both speak SQL, yet anyone who believes an application can be migrated between the two systems through search and replace will quickly learn otherwise. The reason lies in their history: PostgreSQL was designed from the start as an object relational system with strict typing and close adherence to the SQL standard. MySQL grew out of the need for a fast, easy to operate database server for web applications and built in many pragmatic shortcuts that later became conventions of their own.

This differing philosophy shows up almost everywhere: in how data types are handled, in the case sensitivity of table names, in how invalid values are treated, and in support for advanced features such as window functions or recursive common table expressions. Anyone running PostgreSQL and MySQL in parallel, for example because a legacy system runs on MySQL while new microservices run on PostgreSQL, needs to know these differences to avoid being surprised by unexpected behavior in production. The following sections walk systematically through the most practically relevant differences between PostgreSQL and MySQL.

2. Data types: where PostgreSQL and MySQL fundamentally diverge

The first stumbling block between PostgreSQL and MySQL is the data types themselves. PostgreSQL offers a real BOOLEAN type with the values true, false, and null, while MySQL treats BOOLEAN merely as an alias for TINYINT(1). That means a MySQL boolean column can technically accept the value 2 without the database objecting at all. PostgreSQL would consistently reject that value. For applications that depend on strict data integrity, this is a relevant difference between PostgreSQL and MySQL.

Another important difference concerns arrays: PostgreSQL supports native array columns (INTEGER[], TEXT[]), a concept MySQL does not know at all, requiring a separate table or a JSON column as a workaround instead. Enum types differ between the two systems as well: PostgreSQL allows reusable, user defined enum types via CREATE TYPE, while MySQL ties enums directly to the column definition, which makes changes more expensive because the entire column must be altered.

-- PostgreSQL: strict boolean type and reusable enum type
CREATE TYPE order_status AS ENUM ('pending', 'shipped', 'delivered', 'cancelled');

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    status order_status NOT NULL DEFAULT 'pending',
    is_paid BOOLEAN NOT NULL DEFAULT false,
    tags TEXT[] DEFAULT '{}'
);

-- MySQL: enum tied to the column, boolean is just TINYINT(1)
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    status ENUM('pending', 'shipped', 'delivered', 'cancelled') NOT NULL DEFAULT 'pending',
    is_paid TINYINT(1) NOT NULL DEFAULT 0
    -- No native array type, tags need a separate table
);

3. String functions and concatenation syntax compared

Concatenating strings reveals one of the most visible syntax differences between PostgreSQL and MySQL. PostgreSQL follows the SQL standard and uses the || operator, while MySQL traditionally uses the CONCAT() function, because || can be interpreted as a logical OR in MySQL depending on the SQL mode. Anyone who wants to port code between both systems should consistently use CONCAT(), since PostgreSQL supports this function as well, while MySQL does not interpret the || operator as concatenation by default.

There are also differences in how string comparisons handle case. MySQL compares strings case insensitively by default, provided the column is defined with an appropriate collation such as utf8mb4_general_ci. PostgreSQL compares strings case sensitively by default, and anyone who needs case insensitive comparisons must explicitly use ILIKE instead of LIKE or switch the column to CITEXT. This difference is one of the most common causes of missing query results after a migration from MySQL to PostgreSQL.

-- PostgreSQL: portable concatenation and case-insensitive search
SELECT first_name || ' ' || last_name AS full_name FROM customers;
SELECT * FROM customers WHERE email ILIKE '%@example.com';

-- MySQL: CONCAT works the same in both systems, || does not
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;
SELECT * FROM customers WHERE email LIKE '%@example.com'; -- case-insensitive by default collation

4. LIMIT, OFFSET, and pagination syntax

For simple pagination, PostgreSQL and MySQL are surprisingly similar: both support LIMIT n OFFSET m. The difference only appears in clause ordering and alternative shorthand notations. MySQL additionally allows the shorthand LIMIT offset, count, with a swapped order and a comma instead of the OFFSET keyword, which does not work in PostgreSQL and produces an immediate syntax error when queries are copied between both systems.

A second, more subtle difference concerns LIMIT combined with UPDATE and DELETE. MySQL allows DELETE FROM table LIMIT 10 directly, while PostgreSQL does not know this syntax at all and instead requires a subquery using ctid or a sort column to achieve the same behavior. Anyone writing maintenance scripts for both databases must account for this difference explicitly, or the script will fail on PostgreSQL with a syntax error.

-- PostgreSQL: standard LIMIT/OFFSET, no LIMIT on UPDATE/DELETE
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 40;

DELETE FROM products
WHERE id IN (
    SELECT id FROM products WHERE stock = 0 ORDER BY id LIMIT 10
);

-- MySQL: LIMIT offset, count shorthand and direct LIMIT on DELETE
SELECT * FROM products ORDER BY id LIMIT 40, 20;

DELETE FROM products WHERE stock = 0 ORDER BY id LIMIT 10;

5. Auto increment: SERIAL vs. AUTO_INCREMENT

For automatically incrementing primary keys, PostgreSQL and MySQL use completely different mechanisms. MySQL has the AUTO_INCREMENT column attribute, written directly into the column definition. PostgreSQL historically has the pseudo type SERIAL, which creates a sequence behind the scenes and links the column to it via DEFAULT nextval(...). Since PostgreSQL 10, the recommended, more standard compliant alternative is GENERATED ALWAYS AS IDENTITY, which follows the SQL standard and is largely interchangeable with the original SERIAL.

The practical difference shows up when explicitly inserting values. In MySQL, an explicit value can be inserted into an AUTO_INCREMENT column without issue, and the counter adjusts automatically. In PostgreSQL with GENERATED ALWAYS AS IDENTITY, an explicit insert is forbidden by default and requires the OVERRIDING SYSTEM VALUE clause. Using GENERATED BY DEFAULT AS IDENTITY instead of ALWAYS gives behavior closer to MySQL.

-- PostgreSQL: modern identity column (SQL standard compliant)
CREATE TABLE customers (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);
-- Legacy syntax, still widely used and functionally equivalent
CREATE TABLE customers_legacy (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);

-- MySQL: AUTO_INCREMENT column attribute
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE
);

6. Case sensitivity for identifiers and strings

An often overlooked difference between PostgreSQL and MySQL lies in how case is handled for identifiers such as table and column names. PostgreSQL automatically lowercases unquoted identifiers, so SELECT * FROM Products and SELECT * FROM products are treated identically, unless the table name was quoted with double quotes as "Products" at creation time. MySQL, by contrast, treats table names as case sensitive by default on Linux systems, because they are mapped directly at the filesystem level, but often case insensitive on Windows and macOS.

This inconsistency between operating systems is a common source of bugs that stay unnoticed locally on a Windows development machine but surface immediately on a Linux production server running MySQL. The lower_case_table_names setting in the MySQL configuration controls this behavior, but should never be changed retroactively on an existing system, since that can lead to inconsistent table names. PostgreSQL is more consistent in this regard, because its behavior is always the same regardless of the operating system.

7. JSON support: JSONB vs. JSON column

Both databases support JSON data, but with different depth. PostgreSQL offers two JSON types: JSON, which stores the text unchanged, and JSONB, which stores the content in a binary, indexable format. JSONB is the right choice for nearly all practical use cases, because it supports GIN indexes for fast lookups on individual JSON keys and provides operators such as ->, ->>, and @> for convenient querying.

MySQL has had a native JSON data type since version 5.7, which is also stored internally in binary form, but works with different operator syntax: JSON_EXTRACT() or the shorthand -> for accessing values, and ->> for unquoted text extraction. Functionally, both systems have grown closer together, but the concrete function names and indexing strategies still differ, meaning JSON heavy queries almost always need to be rewritten during a migration between PostgreSQL and MySQL.

-- PostgreSQL: JSONB with GIN index and containment operator
CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,
    payload JSONB NOT NULL
);
CREATE INDEX idx_events_payload ON events USING GIN (payload);

SELECT payload->>'event_type' AS event_type
FROM events
WHERE payload @> '{"source": "checkout"}';

-- MySQL: JSON type with generated column for indexing
CREATE TABLE events (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    payload JSON NOT NULL,
    event_type VARCHAR(64) AS (payload->>'$.event_type') STORED,
    INDEX idx_event_type (event_type)
);

SELECT payload->>'$.event_type' AS event_type
FROM events
WHERE JSON_CONTAINS(payload, '"checkout"', '$.source');

8. Upsert syntax: ON CONFLICT vs. ON DUPLICATE KEY UPDATE

For cases where an insert should trigger an update on an existing row instead, both databases offer their own upsert syntax, and these differ fundamentally. PostgreSQL uses the clause INSERT ... ON CONFLICT (column) DO UPDATE SET ..., which explicitly states which unique constraint the conflict refers to. MySQL uses INSERT ... ON DUPLICATE KEY UPDATE ..., which implicitly reacts to any violated unique or primary key constraint on the table, without needing to name the affected column explicitly.

This difference has practical consequences: in PostgreSQL, you can decide precisely that a conflict on a particular unique index should be ignored (DO NOTHING), while a conflict on a different constraint throws an error. MySQL does not offer this granularity, the update clause applies whenever any key is violated. Anyone who wants to keep upsert logic portable between both systems must reflect this semantic difference in the application logic itself, since a plain syntax translation is not enough.

-- PostgreSQL: explicit conflict target, precise control
INSERT INTO product_stats (product_id, view_count)
VALUES (42, 1)
ON CONFLICT (product_id)
DO UPDATE SET view_count = product_stats.view_count + 1;

-- MySQL: implicit conflict on any unique/primary key violation
INSERT INTO product_stats (product_id, view_count)
VALUES (42, 1)
ON DUPLICATE KEY UPDATE view_count = view_count + 1;

9. PostgreSQL vs. MySQL side by side

The following table summarizes the most important syntax differences between PostgreSQL and MySQL that most commonly cause migration errors in daily practice.

This table shows exemplarily why a blanket search and replace does not work when migrating between PostgreSQL and MySQL. Every one of these points requires a conscious decision in the code, not just a mechanical translation of syntax. Teams that support both databases in parallel benefit from using an abstraction layer such as a query builder or ORM that encapsulates these differences, instead of scattering raw SQL throughout the entire codebase.

10. Summary

The comparison between PostgreSQL and MySQL shows that although both systems share the same SQL standard as a common foundation, their practical implementations differ significantly. Data types such as BOOLEAN and arrays are handled with different strictness, auto increment columns follow different mechanisms, and upsert syntax as well as JSON operators each require their own code. Knowing these differences before a migration begins saves weeks of debugging in production.

The most important practical advice: when running PostgreSQL and MySQL in parallel, for example in a microservice landscape with multiple teams, a documented translation table in the team wiki is worthwhile, complemented by automated tests that cover exactly the edge cases described in this article. That turns a potential production bug into a known, documented difference between PostgreSQL and MySQL that every team member can look up.

11. FAQ: PostgreSQL vs. MySQL Syntax Differences