Post-MySQL

Table of contents

Avoid worst MySQL quirks
1. Everyone else's SQL
Functions
Types
Upsert
Triggers

For trivial CRUD applications it might be enough to use a database abstraction layer, but anything more complicated needs conversion.

Avoid worst MySQL quirks

First ensure that the application works in MySQL's “ANSI” mode. Start every connection with:

SET SESSION sql_mode='ANSI';
SET NAMES utf8;

It's like DOCTYPE for MySQL. Disables all worst quirks and makes it do best effort to support standard SQL syntax. This allows basic queries to be compatible with both MySQL and PostgreSQL, but you'll have to remove MySQL-isms from the queries first:

Everyone else's SQL

Strings are in single quotes, not double quotes. Pair of single quotes is interpreted as single quote in the value. Escaping of backslashes is a hairy problem (depends on settings). Ideally you should stick to prepared statements and/or built-in functions for string escaping that make this non-issue.
```
"It\'s a string" → 'It''s a string'
```

String concatenation is done with the || operator, not CONCAT().
```
CONCAT(foo,bar) → foo || bar
this || that → this OR that
```

Column names are escaped with double quotes, not backticks. Be warned that unquoted column names in PostgreSQL are case-insensitive and are not case-preserving (will be lowercased), so you might need to quote every name that uses upper case.
```
`name` → "name"
```

Functions

If you want to keep MySQL portability or just be done with porting quickly, try mysqlcompat — a reimplementation of MySQL-specific functions in PostgreSQL.

I don't recommend importing all of them at once (MySQL has some crazy stuff, and mysqlcompat will shadow PostgreSQL's built-in functions/operators), but picked selectively are a huge time-saver.

Types

Helpful mapping of MySQL to PostgreSQL types.

Use text type for all text. PostgreSQL doesn't have any faster handling for varchar, and unlike MySQL will strongly enforce the length limit (which breaks applications using MySQL to truncate data).

There are no unsigned types. You can add CHECK(value >= 0) if you want to enforce sign, but you're not getting that bit of storage back.
If you've used INT UNSIGNED and inet_aton() to store IP addresses, you need BIGINT or native IP data type now.

There's no SET() type. Bummer. Dust off your bit-twiddling knowledge (using integer column) or store set in a relation (separate table).

Instead of AUTO_INCREMENT there are sequences, and they're awesome. Only catch is that API like last_insert_id() will need name of the sequence (usually table_column_seq).

Upsert

Kill me now. Unfortunately PostgreSQL doesn't have REPLACE INTO, INSERT IGNORE or ON DUPLICATE KEY UPDATE.

It pretends there's no problem, because in v9 it has MERGE (all hail SQL 2003 standard!), but that's only a partial solution, and its syntax is awful. Still, if you can, use MERGE, because none its alternatives are any better.

REPLACE INTO can be emulated with DELETE and INSERT in a transaction (just be careful not to delete too much).

For INSERT IGNORE use temporary table, insert all data into it, and then copy with INSERT … SELECT statement that excludes duplicates.

If you always INSERT IGNORE on a table, then try this:

CREATE OR REPLACE RULE insert_ignore AS ON INSERT TO table
WHERE NEW.id = OLD.id
DO INSTEAD NOTHING;

Here's one way to emulate ON DUPLICATE KEY UPDATE in Pg/PLSQL (in a trigger or a function):

UPDATE foo SET bar=NEW.bar WHERE baz=NEW.baz
IF NOT FOUND THEN
  BEGIN
    INSERT INTO foo(bar,baz) VALUES (NEW.bar,NEW.baz);
    EXCEPTION WHEN unique_violation THEN
      -- Oops, race condition. You should have locked the table.
    END;
  END IF;
END;

The same thing can be emulated in application logic by checking number of rows that UPDATE query touched or even using UPDATE … RETURNING id. It sucks, but so does PostgreSQL's upsert support.

Triggers

This:

DELIMITER ;;
CREATE TRIGGER foo BEFORE INSERT ON table FOR EACH ROW
SET NEW.foo = 'bar'
;;
DELIMITER ;

changes to this:

CREATE LANGUAGE plpgsql; -- needed only once
CREATE OR REPLACE FUNCTION foo() RETURNS trigger AS
$BODY$
    BEGIN
        NEW.foo := 'bar';
        return NEW;
    END;
$BODY$
LANGUAGE plpgsql;
CREATE TRIGGER foo BEFORE INSERT ON table FOR EACH ROW EXECUTE PROCEDURE foo();