Creating a PostgreSQL and MySQL driver
By Tony Marston2005-09-20
CREATE TABLE syntax
|
|
Although both MySQL and PostgreSQL claim to be SQL compliant, there are some differences in the syntax of the CREATE TABLE command:
- Does not like IF NOT EXISTS on CREATE TABLE.
- Does not like ticks around table or column names, wants either nothing or double quotes.
- Does not like UNIQUE to specify a unique key, wants a separate ADD CONSTRAINT statement instead.
- Does not like KEY to specify an index, wants a separate CREATE INDEX statement instead.
- Does not like a column type of DATETIME, wants TIMESTAMP instead.
- Does not like a column type of TINYINT, will accept SMALLINT instead.
- Does not like a column type of MEDIUMINT, will accept BIGINT instead.
- Does not like a column type of DOUBLE, will accept DOUBLE PRECISION instead.
- Does not like size after integer column type.
- Does not like the qualifier UNSIGNED or ZEROFILL on any numeric column type.
- Does not like '0000-00-00 00:00:00' as the default for TIMESTAMP columns, will only accept a valid date and time.
- Does not like TINYTEXT, MEDIUMTEXT or LONGTEXT, wants nothing but TEXT instead.
This means that the following in MySQL:
CREATE TABLE IF NOT EXISTS `tablename` (
`field1` varchar(8) NOT NULL default '',
`field2` tinyint(4) unsigned default NULL,
`field3` smallint(3) unsigned default NULL,
`field4` mediumint(8) unsigned default NULL,
`field5` double default NULL,
`field6` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`field1`),
UNIQUE KEY (`field2`),
KEY `field2` (`field3`)
);
will have to be changed to work in PostgreSQL, as follows:
CREATE TABLE tablename (
field1 varchar(8) NOT NULL default '',
field2 smallint default NULL,
field3 smallint default NULL,
field4 bigint default NULL,
field5 double precision default NULL,
field6 timestamp NOT NULL default '2000-01-01 00:00:00',
PRIMARY KEY (field1)
);
ALTER TABLE tablename ADD CONSTRAINT tablename_field2_key UNIQUE (field2);
CREATE INDEX tablename_index(n) ON tablename (field3);
Tutorial Pages:
» Introduction
» Administrative programs
» Connecting to a Database
» CREATE TABLE syntax
» SQL queries
» Conclusion
|
