Convert MySQL to PostgreSQL
A free online converter that translates MySQL queries into PostgreSQL. Paste your SQL, press Translate, and Query Studio rewrites the syntax that differs between the two databases — instantly, with no login and nothing stored.
The most common migration in this list, and among the more involved. MySQL and PostgreSQL agree on the shape of a SELECT and disagree about almost everything around it — quoting, type system, strictness, and what the database will let you get away with. The syntax translation is the easy half.
MySQL → PostgreSQL example
Here is a real MySQL query and the PostgreSQL output Query Studio produces:
SELECT u.id, u.name, COUNT(o.id) AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2024-01-01' AND u.active = 1
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 3
ORDER BY orders DESC
LIMIT 10;SELECT
u.id,
u.name,
COUNT(o.id) AS orders
FROM
users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE
u.created_at > '2024-01-01'
AND u.active = 1
GROUP BY
u.id,
u.name
HAVING
COUNT(o.id) > 3
ORDER BY
orders DESC
LIMIT
10;What changes from MySQL to PostgreSQL
- Identifier quoting is rewritten from backtick `identifiers` to double-quoted "identifiers".
- Null-handling functions such as IFNULL() are mapped to COALESCE().
- The result is re-indented and keyword-cased in PostgreSQL style so it's ready to paste and run.
MySQL to PostgreSQL data type mapping
Query Studio translates queries, not schemas. When you come to move the tables themselves, this is what changes between MySQL and PostgreSQL:
| MySQL | PostgreSQL | Watch out for |
|---|---|---|
| INT AUTO_INCREMENT | INTEGER GENERATED ALWAYS AS IDENTITY | SERIAL still works and is shorter, but IDENTITY is the SQL-standard form and does not leave an ownerless sequence behind if the column is dropped. |
| TINYINT(1) | BOOLEAN | MySQL has no real boolean — TINYINT(1) is the convention. Any code comparing the column to 0 or 1 has to change to FALSE/TRUE. |
| DATETIME | TIMESTAMP | Neither carries a time zone. Consider TIMESTAMPTZ instead; it is almost always what was meant. |
| TIMESTAMP | TIMESTAMPTZ | MySQL's TIMESTAMP converts to UTC on write and back on read. TIMESTAMPTZ is the closest equivalent; plain TIMESTAMP is not. |
| ENUM('a','b') | CREATE TYPE … AS ENUM / CHECK constraint | Postgres enums are standalone types created before the table. A CHECK constraint on TEXT is easier to alter later. |
| DOUBLE | DOUBLE PRECISION | Direct equivalent. |
| LONGTEXT / MEDIUMTEXT / TEXT | TEXT | Postgres has one unbounded text type and no length tiers. |
| BLOB / LONGBLOB | BYTEA | Direct equivalent. |
| UNSIGNED INT | BIGINT + CHECK (col >= 0) | Postgres has no unsigned types. Widening to BIGINT preserves the range; the CHECK preserves the intent. |
| JSON | JSONB | JSONB is binary, indexable and reorders keys. Use JSON only if byte-for-byte round-tripping matters. |
| DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | A BEFORE UPDATE trigger | Postgres has no ON UPDATE clause; the auto-updating timestamp needs a trigger function. |
MySQL to PostgreSQL: what actually catches people out
Syntax is the part a translator can fix. These are the differences that survive a clean conversion and show up later as wrong results rather than as errors.
Identifier case folding will break queries that looked fine
MySQL on Linux is case-sensitive for table names and case-insensitive for column names. PostgreSQL folds every unquoted identifier to lowercase, and treats a double-quoted identifier as case-sensitive and literal. So a table created as "UserAccounts" can only ever be referenced as "UserAccounts"; writing UserAccounts unquoted looks for useraccounts and fails. The safest migration is to lowercase every identifier and never quote them again.
SELECT `userId` FROM `UserAccounts`;SELECT user_id FROM user_accounts;GROUP BY is strict, and your queries probably are not
MySQL historically let you SELECT columns that were neither aggregated nor grouped, returning an arbitrary row's value. PostgreSQL rejects that outright with "column must appear in the GROUP BY clause or be used in an aggregate function". This is the single most common source of queries that break after a migration, and it is usually a latent bug rather than a syntax problem — the query was returning arbitrary data all along.
SELECT user_id, name, COUNT(*) FROM orders GROUP BY user_id;SELECT user_id, MIN(name) AS name, COUNT(*) FROM orders GROUP BY user_id;Zero dates do not exist in PostgreSQL
MySQL accepts '0000-00-00' as a date and many older schemas are full of them. PostgreSQL has no such value and the import will fail on the first one. Convert them to NULL before the data move, not after — and make the column nullable if it was NOT NULL DEFAULT '0000-00-00'.
ON DUPLICATE KEY UPDATE becomes ON CONFLICT
The upsert is spelled completely differently and, unlike MySQL's version, PostgreSQL requires you to name the constraint or the columns that define the conflict. That is a real improvement — MySQL's form fires on whichever unique index happens to be violated, which is ambiguous on a table with several.
INSERT INTO t (id, n) VALUES (1, 5)
ON DUPLICATE KEY UPDATE n = n + 1;INSERT INTO t (id, n) VALUES (1, 5)
ON CONFLICT (id) DO UPDATE SET n = t.n + 1;Implicit type coercion stops happening
MySQL will compare a string to a number, silently casting as it goes: WHERE id = '42' works, and so does the far worse WHERE id = '42abc'. PostgreSQL raises a type error instead. Any ORM or hand-written query that relies on coercion needs an explicit cast — and any place this was silently succeeding on garbage input is worth looking at properly.
Backticks are not valid SQL anywhere else
MySQL's backtick quoting is a MySQL invention. PostgreSQL uses double quotes, which in MySQL mean a string literal unless ANSI_QUOTES is set. This is the one thing every conversion has to fix, and it is what the translator above handles first.
What this converter will not do
Query Studio translates SQL syntax. Being honest about the boundary is more useful than claiming there isn’t one — and on MySQL → PostgreSQL specifically, these are the three that matter most:
Stored procedures, functions and triggers
Procedural code — PL/pgSQL, T-SQL procedures, MySQL routines — is a different language in every engine, with different control flow, error handling, variable declaration and transaction semantics. Query Studio translates queries, not programs. These have to be ported by hand.
Anything that depends on data rather than syntax
Whether a value fits the target type, whether a date is real, whether a text column's contents are valid UTF-8 — none of that is visible in the query. A translation can be syntactically perfect and still fail on the first row of the import.
Vendor-specific extensions
PostGIS geometry, MySQL spatial functions, SQL Server's FOR XML and hierarchyid, BigQuery's nested/repeated model, Snowflake's time travel. Where there is no equivalent concept, there is no translation — only a redesign.
The full list of what a syntax translator cannot do is on the Query Studio page.
How to convert MySQL to PostgreSQL
- Open the Query Studio editor and choose MySQL as the “From” dialect.
- Choose PostgreSQL as the “To” dialect.
- Paste your MySQL query and press Translate — copy the PostgreSQL result.
Try it with your own query
The editor is preloaded with MySQL → PostgreSQL. You can also explain, format, validate and analyze the result.
Convert MySQL to PostgreSQL now →Working with the data rather than the schema? Open a large CSV, JSON or Parquet file and query it with SQL — no upload, no row limit, and files far past what Excel will open.
MySQL to PostgreSQL FAQ
Is this MySQL to PostgreSQL converter free?
Yes — it's completely free with no account, no sign-up and no usage limits. Your query is processed to return the result and never stored.
Is the MySQL to PostgreSQL conversion accurate?
Query Studio rewrites syntax deterministically using real SQL parsers, so it gives the same result every time — there is no AI involved and no variation between runs. It handles the differences listed above automatically. What it cannot do is anything semantic: stored procedures, triggers, vendor extensions and performance characteristics all need a human. Review complex, vendor-specific queries before running them in production.
What breaks when migrating from MySQL to PostgreSQL?
The differences that most often cause problems on this pair: Identifier case folding will break queries that looked fine; GROUP BY is strict, and your queries probably are not; Zero dates do not exist in PostgreSQL; ON DUPLICATE KEY UPDATE becomes ON CONFLICT; Implicit type coercion stops happening; Backticks are not valid SQL anywhere else. Each is explained in full above, with before-and-after examples where seeing it is quicker than reading about it.
How do MySQL data types map to PostgreSQL?
The full mapping table is above and covers 11 types. The ones that are not a straight rename: INT AUTO_INCREMENT → INTEGER GENERATED ALWAYS AS IDENTITY, TINYINT(1) → BOOLEAN, DATETIME → TIMESTAMP, TIMESTAMP → TIMESTAMPTZ. Note that Query Studio translates queries rather than schemas — the table is there to tell you what your CREATE TABLE statements need, not to rewrite them for you.
Does this MySQL to PostgreSQL converter use AI?
No. Every result is computed by real SQL parsers and rule engines, which is what makes it free, instant, unlimited and identical on every run. Nothing is sent to a model, so there are no rate limits, no per-request cost to pass on, and no possibility of a confidently wrong answer that looks plausible.
Can I convert PostgreSQL back to MySQL?
Yes. Use the PostgreSQL to MySQL converter, or press the Swap (⇄) button inside the editor to flip the direction instantly. Note that a round trip is not guaranteed to return your original text — both directions normalise formatting, and where one dialect has no equivalent for a feature the information is genuinely lost rather than recoverable.