notas:bases_de_datos
¡Esta es una revisión vieja del documento!
Tabla de Contenidos
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
notas/bases_de_datos.1269870573.txt.gz · Última modificación: 2010/03/29 13:49 por cayu