MySQL and PostgreSQL both use SQL, but they do not always interpret it in the same way.
A query can look perfectly normal in MySQL and still fail, or behave differently, after being moved to PostgreSQL.
Here are seven differences worth checking during a migration.
1. Identifier quoting
MySQL commonly uses backticks around table and column names:
SELECT `name`, `order`
FROM `customers`;
PostgreSQL uses double quotes for quoted identifiers:
SELECT "name", "order"
FROM "customers";
Quoting becomes especially important when an identifier contains spaces, uppercase letters, or reserved words.
A better long-term approach is to use simple lowercase identifiers that do not require quoting whenever possible.
2. Automatically generated IDs
A MySQL table may use AUTO_INCREMENT:
CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100)
);
A modern PostgreSQL equivalent can use an identity column:
CREATE TABLE customers (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name VARCHAR(100)
);
PostgreSQL also supports SERIAL, but identity columns provide clearer standards-based behavior for new schemas.
After migrating data, verify that the PostgreSQL sequence continues after the highest imported ID.
3. Boolean values
MySQL applications often represent booleans with TINYINT:
CREATE TABLE users (
is_active TINYINT(1)
);
PostgreSQL has a dedicated BOOLEAN type:
CREATE TABLE users (
is_active BOOLEAN
);
Values such as 1 and 0 may need to become TRUE and FALSE.
This difference can also affect application code, filters, default values, and imported data.
4. Handling NULL values
MySQL provides IFNULL:
SELECT IFNULL(phone_number, 'Not provided')
FROM customers;
PostgreSQL commonly uses COALESCE:
SELECT COALESCE(phone_number, 'Not provided')
FROM customers;
COALESCE is supported by both databases and can make future migrations easier.
NULL behavior should still be tested carefully in comparisons, concatenation, calculations, and conditional expressions.
5. Date arithmetic
MySQL can add seven days with DATE_ADD:
SELECT DATE_ADD(order_date, INTERVAL 7 DAY)
FROM orders;
PostgreSQL can express the same operation with an interval:
SELECT order_date + INTERVAL '7 days'
FROM orders;
The visible syntax is only part of the difference.
Also verify date types, timestamps, time zones, month boundaries, and daylight-saving behavior when they matter to the application.
6. Concatenating strings
MySQL commonly uses CONCAT:
SELECT CONCAT(first_name, ' ', last_name)
FROM customers;
PostgreSQL supports CONCAT, but it also commonly uses the double-pipe operator:
SELECT first_name || ' ' || last_name
FROM customers;
Be careful when values can be NULL. Different concatenation methods may produce different results depending on the inputs.
Test names, addresses, report labels, and other expressions that combine multiple columns.
7. Upsert syntax
MySQL can insert or update a row with ON DUPLICATE KEY UPDATE:
INSERT INTO products (id, name, price)
VALUES (10, 'Keyboard', 49.99)
ON DUPLICATE KEY UPDATE
name = VALUES(name),
price = VALUES(price);
PostgreSQL uses ON CONFLICT:
INSERT INTO products (id, name, price)
VALUES (10, 'Keyboard', 49.99)
ON CONFLICT (id) DO UPDATE SET
name = EXCLUDED.name,
price = EXCLUDED.price;
The PostgreSQL statement identifies the conflict target explicitly.
Review unique constraints and indexes before converting upserts because they determine which conflicts PostgreSQL can detect.
What else should be tested?
Syntax conversion is only the first step.
After converting a MySQL query or schema, compare the source and target behavior using representative data.
Check:
• Row counts
• NULL values
• Generated IDs
• Date and time results
• Decimal precision
• Case sensitivity
• Sort order
• Duplicate handling
• Default values
SQL that runs successfully is not automatically SQL that behaves correctly.
A reliable migration workflow
A practical MySQL-to-PostgreSQL workflow is:
Understand the source SQL
Convert it to PostgreSQL
Review dialect-specific changes
Inspect diagnostics
Test with representative data
Compare the results
Deploy only after validation
Sqlinfy can help create a structured first-pass conversion between MySQL and PostgreSQL. Developers should still review and test the converted SQL before using it in production.
