PostgreSQL/From MySQL
From Jon's Wiki
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
- PostgreSQL doesn't care how small your integers are, unless you want ones > 231 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)