Difference between revisions of "PostgreSQL/From MySQL"

Revision as of 12:39, 6 February 2008

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⁽¹⁾
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⁽²⁾
VARCHAR(n) CHAR(n) LONGTEXT BLOB LONGBLOB	TEXT	Yes⁽³⁾
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⁽⁴⁾
column SET('value1', 'value2')	column TEXT CHECK (column IN ('value1', 'value2'))	No⁽⁵⁾
<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

PostgreSQL doesn't care how small your integers are, unless you want ones > 2³¹ in which case, use BIGINTEGER instead.
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.
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.
Create your database with UTF8 encoding.
As of 8.3 one can now go
CREATE TYPE type AS ENUM ('value1', 'value2')
column type

Useful magic MySQL incantation:

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 mysqldump --disable-keys switch)

@@ Line 35: / Line 35: @@
 === Notes ===
-# PostgreSQL doesn't care how small your integers are, unless you want ones > 2<sup>31</sup> in which case, use biginteger instead.
+# PostgreSQL doesn't care how small your integers are, unless you want ones > 2<sup>31</sup> in which case, use BIGINTEGER instead.
-# 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.
+# 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.
 # 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.
 # Create your database with UTF8 encoding.
-# As of 8.3 one can now go <br /> CREATE TYPE ''<typename>'' AS ENUM ('value1', 'value2') <br /> ''<fieldname>'' ''<typename>''
+# As of 8.3 one can now go <br /> CREATE TYPE ''type'' AS ENUM ('value1', 'value2') <br /> ''column'' ''type''
-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
+Useful magic MySQL incantation:
-For now, remove all KEY and FULLTEXT KEY declarations (or use the --disable-keys switch in mysqldump)
+ 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 mysqldump --disable-keys switch)