Migrating from MySQL to PostgreSQL

by Zach Beane

원본출처 : http://www.xach.com/aolserver/mysql-to-postgresql.html

--------------------------------------------------------------------------------

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. 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.

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

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

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

Dobrica Pavlinusic(http://www.rot13.org/~dpavlin/projects.html) has also written a mysql2pgsql Perl script(http://www.rot13.org/~dpavlin/projects/sql/mysql2pgsql), but it seems to target an older version of PostgreSQL.

'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(http://www.xach.com/aolserver/mysql-functions.sql) that recreate the MySQL functions.

Converted:

ifnull

from_unixtime

unix_timestamp

date_format

if

to_days

from_days

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 (http://www.xach.com/aolserver/mail/names-in-having.txt).

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(http://www.postgresql.org/docs/faq-english.html#4.16.1).