Converting MySQL to PostgreSQL

From Wikibooks, the open-content textbooks collection

Jump to: navigation, search

Contents

[edit] Very Short Intro

You may have read a bunch of short articles with the same name on the web, but there were just snippets of information you needed. It's time to put it all together.

You have a project in MySQL and suddenly you find out that you need to switch to PostgreSQL. Suddenly you see that there are many flavours of SQL and that your seemingly basic constructions throw a lot of errors. You don't have time to really rewrite your code from scratch, it may come later...

Actually, there may be some good reasons to switch...

With PostgreSQL you may still feel a little like a second-class citizen, but not really the ignored one. There are some major projects like Asterisk, Horde or DBMail that have recognized its qualities and although MySQL was their first choice database, they are showing effort to make things running here too.

[edit] Check The Server Running

Most likely you don't need this chapter, but very briefly: after you've installed your package with PostgreSQL on your Linux machine (be it from a package or following these notes), you need to do something like

su -

su - postgres

createdb test

psql test

=# create user username password ' password ';

=# create database databasename with encoding 'utf8';

=# grant all privileges on database databasename to username;

=# \l

=# \c databasename

=# \q

vi /etc/postgresql/pg_hba.conf

host    all         all         0.0.0.0           0.0.0.0            password

be SURE to cover this security issue with iptables!

/etc/init.d/postgresql reload or /usr/lib/postgresql/bin/pg_ctl reload

postmaster successfully signaled

psql -h server -d databasename -U username -W

databasename=>

[edit] Convert and Import

Dump your tables with

mysqldump --compatible=postgresql databasename > outputfile.sql

but even then you will have to change quote escaping:

sed "s/\\\'/\'\'/g" outputfile.sql

You also have to manually modify the data types etc. as discussed later.

After you convert your tables, import them the same way you were used to in MySQL, that is

psql -h server -d databasename -U username -W < data.sql

[edit] The Environment

[edit] Perl

You will need to install an appropriate DBD package. In Debian/Ubuntu run apt-get install libdbd-pg-perl.

[edit] Changing The Code Quick And Dirty

[edit] Perl

MySQL PostgreSQL comments 
$db=DBI->connect("dbi:mysql:database= ... )

$db=DBI->connect("dbi:Pg:database= ... )
All you have to do is changing mysql to Pg. Beware the case sensitivity.

[edit] SQL

[edit] Syntax

MySQL PostgreSQL comments 
#

--
MySQL uses nonstandard # to begin a comment line; PostgreSQL uses ANSI standard double dash; use the ANSI standard, both databases understand it. 
' " vs. `

' vs. "
MySQL uses ' or " to quote values (i.e. WHERE name = "John"). This is not the ANSI standard for databases. PostgreSQL uses only single quotes for this (i.e. WHERE name = 'John'). Double quotes are used to quote system identifiers; field names, table names, etc. (i.e. WHERE "last name" = 'Smith'). MySQL uses ` (accent mark or backtick) to quote system identifiers, which is decidedly non-standard. Note: you can make MySQL interpret quotes like PostgreSQL using SET sql_mode='ANSI_QUOTES'. 
... WHERE lastname="smith"

... WHERE lower(lastname)='smith'
PostgreSQL is case-sensitive for string comparisons. The field "Smith" is not the same as the field "smith". This is a big change for many users from MySQL and other small database systems, like Microsoft Access. In PostgreSQL, you can either:
  • Use the correct case in your query. (i.e. WHERE lastname='Smith')
  • Use a conversion function, like lower() to search. (i.e. WHERE lower(lastname)='smith')
  • Use a case-insensitive operator, like ILIKE or *~
 
`LastName` = `lastname`

and maybe not?

 
"LastName" <> "lastname"
Database, table, field and columns names in PostgreSQL are case-independent, unless you created them with double-quotes around their name, in which case they are case-sensitive. In MySQL, table names can be case-sensitive or not, depending on which operating system you are using.
Note that PostgreSQL actively converts all non-quoted names to lower case and so returns lower case in query results!		 
'foo' || 'bar'
means OR		 
'foo' || 'bar'
means string concatenation (= 'foobar')		 MySQL uses C-language operators for logic, SQL requires AND, OR.

[edit] Data Types

The ideas for this table were partially derived from automated dump converting script [1]. Official documentation:

List of available data types can be reached also by using psql's internal slash command \dT.

MySQL PostgreSQL ANSI Standard SQL comments 
TINYINT
SMALLINT
MEDIUMINT
BIGINT

SMALLINT
SMALLINT
INTEGER
BIGINT

INTEGER
INTEGER
INTEGER
NUMERIC(20)
see [2]; integer size in PostgreSQL is 4 Bytes signed (-2147483648 – +2147483647) 
TINYINT UNSIGNED
SMALLINT UNSIGNED
MEDIUMINT UNSIGNED
INT UNSIGNED
BIGINT UNSIGNED

SMALLINT
INTEGER
INTEGER
BIGINT
NUMERIC(20)

INTEGER
INTEGER
INTEGER
NUMERIC(10)
NUMERIC(20)
SQL doesn't know UNSIGNED, all numbers are signed. 
ZEROFILL
No replacement. 
DOUBLE

DOUBLE PRECISION

FLOAT8

BOOLEAN

BOOLEAN

BOOLEAN
MySQL Booleans are an alias for TINYINT(1); PostgreSQL doesn't auto-convert numbers into booleans. 
DATE
TIME
DATETIME

DATE
TIME
TIMESTAMP

(none)
TIME
TIMESTAMP

TIMESTAMP NOT NULL DEFAULT NOW()

TIMESTAMP NOT NULL DEFAULT NOW()

TIMESTAMP NOT NULL DEFAULT NOW()

LONGTEXT
MEDIUMTEXT
BLOB

TEXT
TEXT
BYTEA

TEXT

columnname INT AUTO_INCREMENT

columnname SERIAL

equals to:

CREATE SEQUENCE tablename_columnname_seq;
CREATE TABLE tablename ( 
  columnname INT4 NOT NULL
    DEFAULT nextval('tablename_columnname_seq')
);


columnname SERIAL
SERIAL = 1 – 2147483647
BIGSERIAL = 1 – 9223372036854775807

SERIAL is in fact an entity named SEQUENCE. It exists independently on the rest of your table. If you want to cleanup your system after dropping a table, you also have to DROP SEQUENCE tablename_columnname_seq. More on that topic...

 
field ENUM (val_1, val_2, ...)

field VARCHAR(255) NOT NULL,
CHECK (field IN (val_1, val_2, ...))

OR

CREATE TYPE mood AS ENUM ('sad','ok','happy');
  CREATE TABLE person ( current_mood mood ...

field VARCHAR(255) NOT NULL,
CHECK (field IN (val_1, val_2, ...))
PostgreSQL doesn't have the ENUM types prior to 8.3, so you need to simulate it with contraints when using < 8.3.

[edit] Language Constructs


MySQL PostgreSQL comments 
DESCRIBE tablename

\d tablename
from psql

or

SELECT
	a.attname AS Field,
	t.typname || '(' || a.atttypmod || ')' AS Type,
	CASE WHEN a.attnotnull='t' THEN 'YES' ELSE 'NO' END as Null,
	CASE WHEN r.contype='p' THEN 'PRI' ELSE '' END as Key,
	(SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid), '\'(.*)\'')
	   FROM pg_catalog.pg_attrdef d
	   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) AS Default,
	'' as Extras
FROM
	pg_class c 
	JOIN pg_attribute a ON a.attrelid = c.oid
	JOIN pg_type t ON a.atttypid = t.oid
	LEFT JOIN pg_catalog.pg_constraint r ON c.oid=r.conrelid 
		AND r.conname = a.attname
WHERE
	c.relname = 'tablename'
	and a.attnum > 0
	
ORDER BY a.attnum
PostgreSQL doesn't implement an SQL extension; it uses psql's internal slash command instead. (Be careful: in the mysql client, \d is shorthand for DROP TABLE) 
DROP TABLE IF EXISTS

DROP TABLE IF EXISTS
In PostgreSQL-8.2 now added IF EXISTS clause for dropping. Do not throw an error if the database does not exist. A notice is issued in this case. 
REPLACE [INTO] tablename [(columns)] VALUES (values)

or

INSERT INTO table (columns)
VALUES (values)
ON DUPLICATE KEY UPDATE
  column1=value1, column2=value2

CREATE FUNCTION someplpgsqlfunction() RETURNS void AS $$
BEGIN
IF EXISTS( SELECT * FROM phonebook
WHERE name = 'john doe' ) THEN
  UPDATE phonebook
  SET extension = '1234' WHERE name = 'john doe';
ELSE
  INSERT INTO phonebook VALUES( 'john doe','1234' );
  END IF;
  RETURN;
END;
$$ LANGUAGE plpgsql;
PostgreSQL doesn't implement REPLACE SQL extension. The presented solution uses PL/pgSQL. 
SELECT ... INTO OUTFILE '/var/tmp/outfile'

COPY ( SELECT ... ) TO '/var/tmp/outfile'

SHOW DATABASES
run psql with -l parameter or 
\l
from psql

or

select datname as Database from pg_database
WHERE datistemplate='f'
PostgreSQL doesn't implement an SQL extension. 
SHOW TABLES

\dt
from psql

or

SELECT
  c.relname as Tables_in
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE pg_catalog.pg_table_is_visible(c.oid)
AND c.relkind='r'
AND relname NOT LIKE 'pg_%'
ORDER BY 1
PostgreSQL doesn't implement an SQL extension; it uses psql's internal slash command instead.

[edit] Functions

MySQL PostgreSQL comments
LAST_INSERT_ID() CURRVAL('serial_variable') NOTE: it is not only "subsitute string" solution as you need to know the name of SERIAL variable (unlike AUTO_INCREMENT in MySQL). Also note that PostgreSQL can play with the OID of the last row inserted by the most recent SQL command.

NOTE2: Even better way to replace LAST_INSERT_ID() is creating a rule, because this cannot suffer from race-conditions:

CREATE RULE get_{table}_id_seq AS ON INSERT TO {table} DO SELECT currval('{table}_id_seq'::text) AS id;

(usage is somehow strange, you get a result from an INSERT-statement, but it works very well)

[edit] Common Errors

  • ERROR: relation "something" does not exist - usually table doesn't exist as you probably didn't make it with the new datatypes or syntax. Also watch out for case folding issues; PostgreSQL != postgresql != "PostgreSQL".
  • prepared statement "dbdpg_X" does not exist -
  • You were able to have database named off in MySQL. You can't in PostgreSQL.

[edit] PL/pgSQL

[edit] Install

You have to make it running explicitly for every database:

your_unix$ su - postgres
your_unix$ .../pgsql/bin/createlang plpgsql -h localhost -d databasename

[edit] Running A Function

SELECT definedfunction();

[edit] Administration

To use the same backup technique than used with MySQL:

/dumps/postgresql/*/*.sql.gz {
        daily
        rotate 20
        nocompress
        sharedscripts
        create
        postrotate
                for i in $(su - postgres -c "psql --list -t" | awk '{print $1}' | grep -vE '^$|^template[0-9]'); do
                        if [ ! -e /dumps/postgresql/$i ]; then mkdir -m 700 /dumps/postgresql/$i; fi
                        su - postgres -c "pg_dump $i" | gzip > /dumps/postgresql/$i/$i.sql.gz
                done
        endscript
}
Retrieved from "http://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL"