From Wikibooks, the open-content textbooks collection

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

• you can sell your product with total peace of mind (PostgreSQL is BSD licensed, MySQL is more complicated)
• you can find articles "Converting from MySQL to PostgreSQL" on the web; you won't find any "Converting from PostgreSQL to MySQL"
• PostgreSQL may not be just another lousy database if Skype, Cisco, Juniper, IMDb, Pandora or TV NOVA decided to rely on it and Sun Microsystems made it database of choice (which is explicitly funny because Sun recently acquired MySQL).

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

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

$db=DBI->connect("dbi:Pg:database= ... )

[edit] SQL

[edit] Syntax

#

--

' " vs. `

' vs. "

... WHERE lastname="smith"

... WHERE lower(lastname)='smith'

`LastName` = `lastname`

"LastName" <> "lastname"

'foo' || 'bar'

'foo' || 'bar'

[edit] Data Types

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

• PostgreSQL 8.3 Data Types
• MySQL 5.1 Data Types

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

TINYINT
SMALLINT
MEDIUMINT
BIGINT

SMALLINT
SMALLINT
INTEGER
BIGINT

INTEGER
INTEGER
INTEGER
NUMERIC(20)

TINYINT UNSIGNED
SMALLINT UNSIGNED
MEDIUMINT UNSIGNED
INT UNSIGNED
BIGINT UNSIGNED

SMALLINT
INTEGER
INTEGER
BIGINT
NUMERIC(20)

INTEGER
INTEGER
INTEGER
NUMERIC(10)
NUMERIC(20)

ZEROFILL

DOUBLE

DOUBLE PRECISION

FLOAT8

BOOLEAN

BOOLEAN

BOOLEAN

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

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

columnname SERIAL

field ENUM (val_1, val_2, ...)

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

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

[edit] Language Constructs

• MySQL 5.1 SQL Statement Syntax
• PostgreSQL 8.1 SQL Commands
• PostgreSQL 8.2 SQL Commands

DESCRIBE tablename

\d tablename

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

DROP TABLE IF EXISTS

DROP TABLE IF EXISTS

REPLACE [INTO] tablename [(columns)] VALUES (values)

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;

SELECT ... INTO OUTFILE '/var/tmp/outfile'

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

SHOW DATABASES

\l

select datname as Database from pg_database
WHERE datistemplate='f'

SHOW TABLES

\dt

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

[edit] Functions

• MySQL 5.1 Functions and Operators
• PostgreSQL 8.1 SQL Functions and Operators
• mysqlcompat, a reimplementation of most MySQL functions in PostgreSQL

[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
}