CODEWITHSUNDEEP
mysqlpostgresql
ciphor
ciphor

Reputation: 8288

Is there any tool can translate MySQL SQL to PostgreSQL SQL?

We are migrating our database from MySQL to PostgreSQL, and most of the SQLs in our existing applications need to be rewritten.

I'm wondering if there is any tool that can translate MySQL SQL to PostgreSQL SQL, so that we can avoid to rewrite those SQLs one by one manually.

Upvotes: 1

Views: 181

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324305

SQL is complex enough that a general purpose tool to "understand" an SQL statement and produce a compatible equivalent in another dialect would be impractical. Particularly because most SQL makes implicit assumptions about the data that isn't actually enforced anywhere, so the SQL statement might do one thing on one data set but something different (and unexpected) on on another data set. How would you write a tool to convert MySQL queries that use session variables? Or PostgreSQL queries that use writable common table expressions? There's no way you'll be automating that.

You might be able to find tools to automate simple syntax and dialect changes and common one-for-one function substitutions, but that's about it. Unless you have a huge amount of simple, very similar SQL statements such tools aren't tons of use. If you have huge numbers of very similar and possibly originally machine-generated statements you might want to think about your application design, as that's not really a good approach.

You're generally better off fixing the SQL by hand, possibly after some simple data type substitutions.

It will help you a great deal if you first fix your SQL to work correctly on MySQL run in ANSI and STRICT mode, so you catch some of the really dumb MySQL-isms, you can change your code to ANSI quoting instead of MySQL's quoting rules, etc.

Upvotes: 1

Related Questions