Migrating from MySQL to PostgreSQL

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));

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:

timestamp fields, instead of not null default now(), could be handled by a trigger
Secondary key definitions in MySQL to create indexes in PostgreSQL

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:

ifnull
from_unixtime
unix_timestamp
date_format
if
to_days
from_days

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.

PostgreSQL won't let you use expression aliases in HAVING in queries. In MySQL it's legal to have something like this:
```
   select name, count(*) as score
   from mytable
   group by name
   having score > 10
```
For PostgreSQL, you have to write out the expression again:
```
   select name, count(*) as score
   from mytable
   group by name
   having count(*) > 10
```
It doesn't mind having aliases in ORDER BY, but you can't use it in HAVING. Tom Lane explains.
PostgreSQL has a compile-time limit of 31 characters for names (for tables, columns, sequences, functions, etc). If you get a bit verbose when naming your tables, you have to either truncate them or build PostgreSQL with a longer name limit.
NAMEDATALEN is defined in src/include/postgres_ext.h. The name length is NAMEDATALEN - 1.
PostgreSQL has something similar to MySQL's auto_increment; this is in the FAQ under SERIAL.

xach@xach.com