Migrating from MySQL to PostgreSQL

by Zach Beane

The Most Important Thing

Know SQL. If your only experience with SQL is through MySQL, many things in PostgreSQL (and indeed, any other database system) will seem alien and strange. The idioms you may have developed to work with MySQL may not be the ideal approach when using PostgreSQL. In particular, you have a set of new tools (views, triggers, stored procedures, consistency checks, and more) that you should try to take advantage of.

Get a good book on SQL. The Practical SQL Handbook is pretty handy. Joe Celko's SQL for Smarties, despite the silly title, is a down-to-earth, nuts-and-bolts practical book about using SQL to its fullest. The free online book SQL for Web Nerds, though Oracle-specific, will give you ideas for good idioms.

Converting Dumps

So far I've written a Perl script that converts the data model pretty well. It handles enums (it converts them to varchars with check constraints on the possible values) and auto_increment fields. It also converts MySQL's integer and time types to compatible PostgreSQL types.

The script outputs SQL statements that seem to work with CVS PostgreSQL. The CVS version will eventually become PostgreSQL 7.1.

MySQL PostgreSQL
create table foo (
   id     int not null auto_increment primary key,
   state  enum('enabled', 'disabled')
);

foo's data
create table foo (
   id     int not null primary key default nextval('foo_id_seq'),
   state  varchar(8) check (state in ('enabled', 'disabled'))
);

foo's data

create sequence foo_id_seq;
select setval('foo_id_seq', (select max(id) from foo));

To be handled:

Joseph Speigle wrote to me about an actively-maintained (as of July, 2004) mysql2pgsql Perl script.

'Legacy' Statements

MySQL has some interesting functions that are either unavailable in PostgreSQL, or supported by functions of slightly different syntax. For example, ifnull() in MySQL is similar to coalesce() in PostgreSQL.

To ease the transition from the MySQL that's embedded in all my pages, I've created a set of SQL functions that recreate the MySQL functions.

Converted:

update Christopher Kings-Lynn wrote to tell me about a more thorough project to implement MySQL functions in PostgreSQL. You can find it here.

Other Stuff

Some stuff looks like it just can't be handled by implementing MySQL functions in PostgreSQL. Fundamental differences exist, mainly where PostgreSQL adheres to the SQL standard and MySQL diverges.

This list isn't an endorsement of MySQL; these examples mainly come up because MySQL is much less conformant with SQL standards in general.


xach@xach.com