PostgreSQL/From MySQL

From Jon's Wiki
Revision as of 12:36, 6 February 2008 by Johnno (talk | contribs) (New page: MySQL sucks, so use PostgreSQL instead. The following table shows the sorts of retarded assumptions it makes, and how to get around them. {| border=1 |- | '''MySQL''' || '''PostgreSQL'...)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

MySQL sucks, so use PostgreSQL instead. The following table shows the sorts of retarded assumptions it makes, and how to get around them.

MySQL PostgreSQL Supported by both?
LIMIT x, y LIMIT x OFFSET y Yes
BOOLEAN
INT
INT(n)
INTEGER(n)
TINYINT
TINYINT(n)
TINYINTEGER
TINYINTEGER(n)
MEDIUMINT
MEDIUMINT(n)
MEDIUMINTEGER
MEDIUMINTEGER(n)
SMALLINT
SMALLINT(n)
SMALLINTEGER
SMALLINTEGER(n)
INTEGER Yes(1)
BOOLEAN INTEGER CHECK (fieldname IN ('0', '1')) No
INTEGER UNSIGNED INTEGER CHECK (fieldname > -1) No
INTEGER SIGNED INTEGER Yes
INTEGER AUTO_INCREMENT SERIAL PRIMARY KEY No(2)
VARCHAR(n)
CHAR(n)
LONGTEXT
BLOB
LONGBLOB
TEXT Yes(3)
DATETIME
DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'
TIMESTAMP
TIMESTAMP NOT NULL DEFAULT NOW()
No
'0000-00-00 00:00:00' NOW() No
CHARACTER SET UTF8 COLLATE UTF8_BIN No(4)
column SET('value1', 'value2') column TEXT CHECK (column IN ('value1', 'value2')) No(5)
<columndef> COMMENT comment COMMENT ON COLUMN column IS 'comment' No
[UNIQUE] KEY indexname (column1, column2) CREATE [UNIQUE] INDEX indexname ON tablename (column1, column2) Yes

Notes

  1. PostgreSQL doesn't care how small your integers are, unless you want ones > 231 in which case, use biginteger instead.
  2. The serial type is an auto-incrementing integer sequence (implicitly created at CREATE TABLE time), maintained externally from the table (akin to an index). Autoincrementing integers suck in distributed scenarios, consider a UUID or composite key instead.
  3. PostgreSQL couldn't care less, and stores them all as varying text anyway. Insisting on a length won't make it faster or use less disk space.
  4. Create your database with UTF8 encoding.
  5. As of 8.3 one can now go
    CREATE TYPE <typename> AS ENUM ('value1', 'value2')
    <fieldname> <typename>

mysqldump fez_svn_empty -v -n --compatible=ansi,postgresql --complete-insert=TRUE --extended-insert=FALSE --compact --default-character-set=UTF8 -r fez_svn_empty.sql For now, remove all KEY and FULLTEXT KEY declarations (or use the --disable-keys switch in mysqldump)