Migrating from MySQL to PostgreSQL

The code examples show typical use of SQL, PL/pgSQL, or other command-line statements. They are displayed using a monospace typeface and clearly separated from the normal text.

SELECT firstname FROM customers WHERE lastname = 'Smith';

The following is a list of conventions used in the code examples, their meaning, and an example.

There are some reasons a person might want to switch from MySQL to PostgreSQL. Some are based on technical merit, others on personal feelings.

A big difference is that PostgreSQL is BSD-licensed. This makes a lot of difference for some people. This is but a small reason to make a change. The true reasoning for switching databases comes from the technical side. PostgreSQL, unlike MySQL, is a full-fledged relational database management system (RDBMS). This means PostgreSQL is pushing its way up there with the big names such as: Oracle, IBM, and Sybase.

The reason MySQL took off over PostgreSQL is that it was blazingly fast on the SELECT query side. And given this and the average deployment of MySQL as a backend for websites allowed for a big boost in use. There comes a time, however, when a (beginning) DBA starts to feel the shortcomings of MySQL and desires/needs a real RDBMS.

Both MySQL and PostgreSQL are nowadays available as a native Windows package as well.

PostgreSQL makes a difference between single quotes (') and double quotes (") as the ANSI standard dictates. MySQL does not follow this behaviour and allows you to use both single and double quotes to quote values and tends to favour the backtick (`), whereas it should only be single quotes. Double quotes are meant to be used on quoting field names, system identifiers, table names, and so on.

MySQL tends to ship with a database locale/collation set to be case insensitive. This means that doing a SELECT query on a lastname like: WHERE lastname='Johnson' would match 'Johnson', 'JOHNSON', or 'johnson'. This is solved easily, but depends on what you want to do:

ANSI only allows '--' as a comment delimiter, whereas MySQL supports the shell/perl's '#' use of a comment delimiter. Just use '--' since both understand this.

PostgreSQL has 3 integer types, namely SMALLINT (2 bytes big), INTEGER (INT, 4 bytes big) and BIGINT (8 bytes big). MySQL supports 5, namely: TINYINT (1 byte big), SMALLINT (2 bytes big), MEDIUMINT (3 bytes big), INT (4 bytes big), BIGINT (8 bytes big). So just take the equivalent or the one closest to it to contain the other in. Per the standard, PostgreSQL does not have unsigned integer types, so you'll need to go up to the next-larger type if you have unsigned data in MySQL big enough to overflow the signed types.

You can use PostgreSQL's TEXT type to replace all of MySQL's *TEXT types (TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT).

PostgreSQL's DECIMAL and NUMERIC are equivalent to each other, just like MySQL's.

MySQL supports, as an extension to the SQL92 standard, a synonym for DOUBLE PRECISION using just DOUBLE.

MySQL's DATETIME is an ISO-8601-like date and timestamp format in the form of: YYYY-MM-DD HH:MM:SS. This excludes any timezone information whatsoever. Within PostgreSQL use TIMESTAMP. This automatically makes it TIMESTAMP WITHOUT TIME ZONE.

To confuse matters MySQL also has a date/time type called TIMESTAMP. This type is updated under a number of conditions. PostgreSQL does not support something like this. Most often you can get away with using the PostgreSQL TIMESTAMP type with a default of now() to have the field receive the current date and time. To get the creation time of the table you would need to use DEFAULT TIMESTAMP.

MySQL's schema (and Sybase too) often includes declarations like:

CREATE TABLE blah (
  id SMALLINT NOT NULL PRIMARY KEY
);

PostgreSQL (and Oracle) also support this syntax. Another way to have this working is to define a constraint, e.g.:

CREATE TABLE blah (
  id SMALLINT NOT NULL
);
ALTER TABLE ONLY blah
  ADD CONSTRAINT blah_pk PRIMARY KEY (id);

MySQL also knows a concept of enumerators (enum). Basically you specify an enumerated list of allowed values, e.g.:

CREATE TABLE blah (
  sentence enum('This', 'is', 'a', 'test')
);

With PostgreSQL 8.3 or later, you can use enums, but you need to create the type first. The bright side is, you can then use that type elsewhere without repeating yourself.

CREATE TYPE sentence as ENUM('This', 'is', 'a', 'test');
CREATE TABLE blah (
  foo sentence
);

MySQL has an argument to fields, auto_increment, which automatically increases the value of the field whenever a new entry is made. PostgreSQL does not work like this. Within PostgreSQL (and Oracle) you normally use a SERIAL. See below for an example:

MySQL definition:

CREATE TABLE test (
  id    AUTO_INCREMENT,
  name  TEXT
);
INSERT INTO test (name) VALUES ('a name');

PostgreSQL definition:

CREATE SEQUENCE test_sequence;
CREATE TABLE test (
  id    SERIAL PRIMARY KEY
  name  TEXT
);
INSERT INTO test (name) VALUES ('a name');

MySQL also support the SERIAL datatype nowadays.

ANSI SQL-92 reserves a lot of words that cannot/should not be used as table or column names. Please avoid using these names as this increases portability between different database backends. Different RDBMSes treat the reserved keyword list differently though, so check with the available documentation.

MySQL's IFNULL(x, y) is definitely not equivalent with PostgreSQL's NULLIF(x, y). The first returns x if x is not NULL, otherwise it returns y. PostgreSQL's NULLIF returns a NULL only if both x and y are equal. MySQL also knows a NULLIF which is the same as PostgreSQL's. The equivalent to MySQL's IFNULL would be COALESCE(x, y).

Do note that while MySQL supports N/0 to be equivalent to NULL, apparently, PostgreSQL reports this as a floating point operation division by zero.

MySQL does not do any date checking. February is allowed to have 30 or 31 days and even leap years are not checked so you could also have a day 29 every year. PostgreSQL enforces correct dates.

MySQL has a UNIX_TIMESTAMP('date') which converts date back to seconds since the Unix epoch. PostgreSQL can also do this through an EXTRACT(EPOCH from TIMESTAMP 'date').

MySQL's LENGTH(string) operator is the same as PostgreSQL's CHAR_LENGTH(string);

PostgreSQL does not really have anything like MySQL's LAST_INSERT_ID(). Instead you need to use the sequences. If you have inserted in the same session you can use SELECT currval(pg_get_serial_sequence('your_table','your_column')); or otherwise you can use SELECT last_value from sequence;.