MySQL vs PostgreSQL Syntax: Translate Queries with AI — Then Verify the Results

Moving a database from MySQL to PostgreSQL is two jobs, not one. Migration tools move the schema and the data. What they don't touch is the long tail behind the database: every query your application, reports, and cron jobs wrote in MySQL's dialect still has to be rewritten for PostgreSQL — one by one. That rewriting is SQL query translation, and it is where migrations quietly lose their weeks.

It usually starts innocently. A query that ran for three years on MySQL hits the new database and throws function group_concat(text) does not exist. Fair enough — you translate that call and run it again.

The next query doesn't throw anything. It just returns different numbers.

There is a whole catalog of these differences — function names, pagination forms, quoting rules, operators that silently mean something else. You could learn it. There's a cheat sheet at the bottom of this post if you ever want it.

Or you could skip the homework entirely. Translating between SQL dialects is exactly the kind of work an AI should be doing for you — if it can also prove the translation is right. Here's what that looks like.

Hand it over

The classic employees dataset, migrated from MySQL to PostgreSQL. This query ran on the MySQL side for years:

SELECT COUNT(*) AS departments,
       GROUP_CONCAT(dept_name ORDER BY dept_name SEPARATOR ', ') AS names
FROM departments;

Open the SQL console on the PostgreSQL connection — the AI sees its live schema, so the whole ask is one sentence in Generate SQL, in your own words:

You don't need to know that GROUP_CONCAT is spelled string_agg over there, or which pagination form PostgreSQL refuses — that knowledge is now the assistant's problem. And it doesn't translate from the prompt alone: before writing anything, it reads the live database. Here it is mid-flight, calling describe_table on the actual schema:

Then the translation lands in the editor. Your original ask stays pinned right above it, token usage sits beside it, and nothing is final until you press Keep — or Revert, which restores the query that was there. One click on Run:

Then check both sides at once

The console said the translation runs. But "ran without an error" and "returns the same thing" are different claims — and the second one is the point. Open a Multi-source query session and connect both sides of the migration at once: the MySQL source as my1, the PostgreSQL target as pg1.

You already watched the original and the translation return the same aggregate. Now prove it at the row level — no SQL in the ask, plain language:

"Compare the departments table on my1 with the one on pg1, row by row, matched by dept_no. Show MATCH or the exact difference for each department."

The assistant answers with something you could not paste into either database alone: one federated query — a FULL OUTER JOIN of my1.departments to pg1.departments by dept_no, with a verdict for every row. And it went one step past the ask: it also labels rows that exist on only one side, a case the prompt never mentioned.

Nine departments checked, nine matched — and the aggregates agree: the original and its translation return the same count and the identical sorted list. That is the difference between "the query ran" and "the query is right", proven on the query itself rather than assumed.

One detail keeps this comparison honest: the ORDER BY dept_name inside both aggregations. Leave it out, and GROUP_CONCAT and string_agg are each free to return the same members in a different order — same data, cosmetic difference, and your MATCH column turns into noise.

Everything the assistant executes is a bounded read-only SELECT — it can prove your translation, not damage your data (how that's enforced). Prefer your own AI client? The same works over the MCP server from Claude Code, Cursor, VS Code Copilot, or Codex — that's exactly how every example in this post was verified.

Why the verification step is not optional

Because the worst dialect differences don't throw errors. Run this on both engines:

SELECT 'a' || 'b';

PostgreSQL says ab — double pipes concatenate strings. MySQL, with its default settings, says 0. There, || is logical OR, and two words are not true.

No error. No warning. A query full of || "works" after the move and returns garbage with a green checkmark next to it. Case-sensitivity does the same trick: many MySQL installations compare strings case-insensitively, PostgreSQL commonly compares exactly — so a WHERE filter finds the row on one engine and nothing on the other. An empty result set is not an error. It's just an answer. A wrong one.

The same silent-lie problem exists one level up, in the migrated data itself — row counts that match while a value differs by a cent. That story has its own post: How to verify a database migration before cutover.

This is also why a plain chat window isn't enough. ChatGPT can usually suggest a plausible translation — but it cannot run both versions against your actual databases and show whether they still mean the same thing. You paste, it answers, and you become the test harness.

The cheat sheet you're delegating

For the curious — this is the list the assistant handles so you don't have to. Every row was run live; the error texts are verbatim:

Two entries are upgrades rather than translations: COALESCE and LIMIT count OFFSET offset run identically on both engines — we checked — so once translated, those queries never need translating again. And Postgres even recognizes the MySQL pagination form by name: LIMIT #,# syntax is not supported is an error message written specifically for people arriving from MySQL.

When it's not the query, it's the schema

If the trouble is types, constraints, or moving the database itself, that's a different job with its own guide: MySQL ↔ PostgreSQL Schema Conversion — the real-world guide covers the type mapping in both directions. And use CDC replication to keep data synchronized while you migrate the application queries separately.

Try it on the query you're moving