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;
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
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”
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.
Helpful mapping of MySQL to PostgreSQL types.
texttype 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
inet_aton()to store IP addresses, you need
BIGINTor 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_INCREMENTthere are sequences, and they're awesome. Only catch is that API like
last_insert_id()will need name of the sequence (usually
Kill me now. Unfortunately PostgreSQL doesn't have
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
INSERT in a transaction (just be careful not to delete too much).
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):
IF NOT FOUND THEN
INSERT INTO foo(bar,baz) VALUES (NEW.bar,NEW.baz);
EXCEPTION WHEN unique_violation THEN
â Oops, race condition. You should have locked the table.
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.
DELIMITER ;; CREATE TRIGGER foo BEFORE INSERT ON table FOR EACH ROW SET NEW.foo = 'bar' ;; DELIMITER ;
changes to this:
CREATE OR REPLACE FUNCTION foo() RETURNS trigger AS
NEW.foo := 'bar';
CREATE TRIGGER foo BEFORE INSERT ON table FOR EACH ROW EXECUTE PROCEDURE foo();</pre>