A Wikibookian suggests that this book or chapter be merged into SQL Dialects Reference. Please discuss whether or not this merge should happen on the discussion page.

Very Short Intro[edit]

You may have read a bunch of short articles with the same name on the web, but they 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 decided to rely on it and Sun Microsystems made it database of choice (which is explicitly funny because Sun 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 run here too.

Check The Server Running[edit]

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 ';
-- To change a password:
=# alter role 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

databasename=>

Convert and Import[edit]

Common way with SQL dump[edit]

Dump your tables with

mysqldump -u username -p --compatible=postgresql databasename > outputfile.sql

but even then you will have to change escaped chars (replacing \t with ^I, \n with ^M, single quote (') with doubled single quote and double (escaped) backslash (\\) with a single backslash). This can't be trivially done with sedcommand, you may need to write a script for it (Ruby, Perl, etc). It is much better and proven solution to prepend your dump with the following lines

SET standard_conforming_strings = 'off';
SET backslash_quote = 'on';

These options will force PostgreSQL parser to accept non-ANSI-SQL-compatible escape sequences (Postgre will still issue HINTs on it; you can safely ignore them). Do not set these options globally: this may compromise security of the server!

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 -f data.sql

Export using CSV-files[edit]

When you have a large sql dump and a binary data inside, it will be uneasy to modify the data structure, so there is another way to export your data to PostgreSQL. Mysql have an option to export each tables from database as separate .sql file with table structure and .txt file with table's data in CSV-format:

mysqldump -U username -p -T/path/to/export databasename

Notice that /path/to/export should be writeable by user who runs mysqld, in most case it mysqld. After that you should modify your table structure according PostgreSQL format:

• convert data types
• create separate keys definitions
• replace escape characters

When table structure will be ready, you should load it as it was shown above. You should prepare data files: replace carriage return characters to "\r" and remove invalid characters for your data encoding. Here is an example bash script how you can do this and load all the data in your database:

#!/bin/bash

CHARSET="utf-8" #your current database charset
DATADIR="/path/to/export"
DBNAME="databasename"

for file in `ls -1 $DATADIR/*.txt`; do
  TMP=${file%.*}
  TABLE=${TMP##*/}
  echo "preparing $TABLE"

  #replace carriage return
  sed 's/\r/\\r/g' $file > /tmp/$TABLE.export.tmp

  #cleanup non-printable and wrong sequences for current charset
  iconv -t $CHARSET -f $CHARSET -c < /tmp/$TABLE.export.tmp > /tmp/$TABLE.export.tmp.out

  echo "loading $TABLE"
  /usr/bin/psql $DBNAME -c "copy $TABLE from '/tmp/$TABLE.export.tmp.out'"

  #clean up
  rm /tmp/$TABLE.export.tmp /tmp/$TABLE.export.tmp.out
done

The Environment[edit]

Perl[edit]

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

Changing The Code Quick And Dirty[edit]

Perl[edit]

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

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

SQL[edit]

Syntax[edit]

#

--

' " vs. `

' vs. "

... WHERE lastname="smith"

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

`LastName` = `lastname`

"LastName" <> "lastname"

'foo' || 'bar'

'foo' || 'bar'

Data Types[edit]

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

• PostgreSQL 8.4 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)

FLOAT
FLOAT UNSIGNED

REAL
REAL

FLOAT4
FLOAT4

DOUBLE

DOUBLE PRECISION

FLOAT8

BOOLEAN

BOOLEAN

BOOLEAN

TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT

TEXT
TEXT
TEXT
TEXT

TEXT
TEXT
TEXT
TEXT

BINARY(n)
VARBINARY(n)
TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB

BYTEA
BYTEA
BYTEA
BYTEA
BYTEA
BYTEA

BIT(n)
BIT VARYING(n)
TEXT
TEXT
TEXT
TEXT

ZEROFILL

not available

not available

DATE
TIME
DATETIME
TIMESTAMP

DATE
TIME [WITHOUT TIME ZONE]
TIMESTAMP [WITHOUT TIME ZONE]
TIMESTAMP [WITHOUT TIME ZONE]

DATE
TIME
TIMESTAMP
TIMESTAMP

column SERIAL

column BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE

column INT DEFAULT SERIAL

column INT NOT NULL AUTO_INCREMENT UNIQUE

column SERIAL

CREATE SEQUENCE name;
CREATE TABLE table ( column INTEGER NOT NULL
        DEFAULT nextval(name)
);

column GENERATED BY DEFAULT

column SERIAL PRIMARY KEY

column SERIAL,
PRIMARY KEY(column)

column ENUM (value1, value2, [...])

column VARCHAR(255) NOT NULL,
CHECK (column IN (value1, value2, [...]))

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

column VARCHAR(255) NOT NULL,
CHECK (column IN (value1, value2, [...]))

Language Constructs[edit]

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

DESCRIBE table 

\d table 

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 table 

DROP TABLE IF EXISTS table 

REPLACE [INTO] table [(column, [...])] VALUES (value, [...])

INSERT INTO table (column1, column2, [...])
VALUES
        (value1, value2, [...])
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

SELECT ... LIMIT offset, limit 

SELECT ... LIMIT limit OFFSET offset 

SELECT ... LIMIT limit OFFSET offset 

CREATE TABLE table ( column ... ,
        {INDEX|KEY} [name] (column, [...])
)

CREATE INDEX name ON table (column, [...])

CREATE INDEX name ON table (column, [...])

USE database ;

\c database 

UNLOCK TABLES;

-- nothing

Functions[edit]

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

Common Errors[edit]

• 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 -

PL/pgSQL[edit]

Install[edit]

In versions prior to 9.0, you have to make it available explicitly for every database:

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

Running A Function[edit]

SELECT definedfunction();

Administration[edit]

To use the same backup technique as used with MySQL, in /etc/logrotate.d/postgresql-dumps:

/dumps/postgresql/*/*.dump.gz {
        daily
        rotate 20
        dateext
        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
                        # compress even in custom format, because it can be compressed more
                        su - postgres -c "pg_dump --format=custom $i" | gzip > /dumps/postgresql/$i/$i.dump.gz
                done
        endscript
}
/dumps/postgresql/*/*.sql.gz {
        daily
        rotate 20
        dateext
        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 --format=plain $i" | gzip > /dumps/postgresql/$i/$i.sql.gz
                done
        endscript
}
/dumps/postgresql/*/*.tar.gz {
        daily
        rotate 20
        dateext
        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 --format=tar $i" | gzip > /dumps/postgresql/$i/$i.tar.gz
                done
        endscript
}