Bases De Datos

Equivalencias MySQL - PostgreSQL

Perl DBI

MySQL	PostgreSQL
$db=DBI→connect(“dbi:mysql:database= … )	$db=DBI→connect(“dbi:Pg:database= … )

Sintaxis y lenguaje

MySQL	PostgreSQL
SHOW FULL PROCESSLIST / SHOW PROCESSLIST	SELECT * FROM pg_stat_activity
#	–
… WHERE lastname=“smith”	… WHERE lower(lastname)='smith'
DESCRIBE tablename	\d tablename
DROP TABLE IF EXISTS	DROP TABLE IF EXISTS
SELECT … INTO OUTFILE '/var/tmp/outfile'	COPY ( SELECT … ) TO '/var/tmp/outfile'
SHOW DATABASES	\l
SHOW TABLES	\dt

Tipos de datos

MySQL	PostgreSQL	comments
TINYINT SMALLINT MEDIUMINT BIGINT	SMALLINT SMALLINT INTEGER BIGINT	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)	SQL doesn't know UNSIGNED, all numbers are signed.
ZEROFILL	No replacement.
DOUBLE	DOUBLE PRECISION
BOOLEAN	BOOLEAN	MySQL Booleans are an alias for TINYINT(1); PostgreSQL doesn't auto-convert numbers into booleans.
DATE TIME DATETIME	DATE TIME TIMESTAMP
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') );	SERIAL = 1 – 2147483647BIGSERIAL = 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 ...	PostgreSQL doesn't have the ENUM types prior to 8.3, so you need to simulate it with contraints when using < 8.3.

Ref.: http://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL