Migrating from MySQL to PostgreSQL

mysql database postgresql licensing migration
Steve M picture Steve M · Aug 20, 2008 · Viewed 16.3k times · Source

We are currently using MySQL for a product we are building, and are keen to move to PostgreSQL as soon as possible, primarily for licensing reasons.

Has anyone else done such a move? Our database is the lifeblood of the application and will eventually be storing TBs of data, so I'm keen to hear about experiences of performance improvements/losses, major hurdles in converting SQL and stored procedures, etc.

Edit: Just to clarify to those who have asked why we don't like MySQL's licensing. We are developing a commercial product which (currently) depends on MySQL as a database back-end. Their license states we need to pay them a percentage of our list price per installation, and not a flat fee. As a startup, this is less than appealing.

Answer

Michał Rudnicki picture Michał Rudnicki · Aug 21, 2008

Steve, I had to migrate my old application the way around, that is PgSQL->MySQL. I must say, you should consider yourself lucky ;-) Common gotchas are:

  • SQL is actually pretty close to language standard, so you may suffer from MySQL's dialect you already know
  • MySQL quietly truncates varchars that exceed max length, whereas Pg complains - quick workaround is to have these columns as 'text' instead of 'varchar' and use triggers to truncate long lines
  • double quotes are used instead of reverse apostrophes
  • boolean fields are compared using IS and IS NOT operators, however MySQL-compatible INT(1) with = and <> is still possible
  • there is no REPLACE, use DELETE/INSERT combo
  • Pg is pretty strict on enforcing foreign keys integrity, so don't forget to use ON DELETE CASCADE on references
  • if you use PHP with PDO, remember to pass a parameter to lastInsertId() method - it should be sequence name, which is created usually this way: [tablename]_[primarykeyname]_seq

I hope that helps at least a bit. Have lots of fun playing with Postgres!

Related questions

How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More
How do you write a case insensitive query for both MySQL and Postgres?

I'm running a MySQL database locally for development, but deploying to Heroku which uses Postgres. Heroku handles almost everything, but my case-insensitive Like statements become case sensitive. I could use iLike statements, but my local MySQL database can't handle that. …

Read More
Comparison of database column types in MySQL, PostgreSQL, and SQLite? (Cross-Mapping)

I am trying to find some way to relate column types across the the most used Databases: MySQL, PostgreSQL, and SQLite. Here is what I have so far, but I'm afraid it's not done and I need some people with …

Read More