Helping ordinary people create extraordinary websites!

Creating a PostgreSQL and MySQL driver

By Tony Marston
2005-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:
 5 Votes

You might also want to check these out:


Leave a Comment on "Creating a PostgreSQL and MySQL driver"
You must be logged in to post a comment.

Link to This Tutorial Page!
GET OUR NEWSLETTERS