CODEWITHSUNDEEP
postgresql
snirali
snirali

Reputation: 139

Error while creating table in PostgreSQL while migrating from MySQL

I am Migrating my database from MySQL to PostgreSQL.While creating table I got an error which I can't resolve.My MySQL Query is like this. MYSQL Query

  CREATE TABLE `configuration` (
  `Name` varchar(300) NOT NULL,
  `Value` varchar(300) default NULL,
  `CType` char(1) default NULL,
  `Size` int(11) default NULL,
  `CGroup` varchar(50) default NULL,
  `RestartReq` char(1) NOT NULL default 'Y',
  `Display` char(1) NOT NULL default 'Y',
  PRIMARY KEY  (`Name`),
  KEY `CType` (`CType`),
  CONSTRAINT `configuration_ibfk_1` FOREIGN KEY (`CType`) REFERENCES `conftype` (`CType`)
  )ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin`

PostgreSQL Query

  CREATE TABLE configuration (
  Name varchar(300) PRIMARY KEY,
  Value varchar(300) default NULL,
  CType char(1) default NULL,
  Size integer default NULL,
  CGroup varchar(50) default NULL,
  RestartReq char(1) NOT NULL default 'Y',
  Display char(1) NOT NULL default 'Y',
  KEY CType (CType),
  CONSTRAINT `configuration_ibfk_1` FOREIGN KEY (CType) REFERENCES conftype (CType)
  )

Running File with psql -h localhost -p 5432 -U postgres -f ps.sql testdb Error getting 

  psql:ps.sql:40: ERROR:  syntax error at or near "(" at character 287
  psql:ps.sql:40: LINE 9:   KEY CType ('CType'),

Upvotes: 1

Views: 328

Answers (1)

Patrick
Patrick

Reputation: 32276

From the MySQL documentation:

KEY is normally a synonym for INDEX.

In PostgreSQL you have to create the index separately from the table:

CREATE TABLE configuration (
  name varchar(300) PRIMARY KEY,
  value varchar(300),
  ctype char(1),
  size integer,
  cgroup varchar(50),
  restartreq boolean NOT NULL DEFAULT true,
  display boolean NOT NULL DEFAULT true,
  CONSTRAINT configuration_ibfk_1 FOREIGN KEY (ctype) REFERENCES conftype (ctype)
);

CREATE INDEX conf_key ON configuration(ctype);

A few other points:

  • PostgreSQL identifiers (mainly table and column names) are case-insensitive except when double-quoted. The standard approach is to put identifiers in lower case and keywords in upper case.
  • Using a varchar(300) as a PRIMARY KEY is usually not a good idea for performance reasons. Consider adding a serial type.
  • The default value of a column is NULL when nothing is specified, so no need to specify DEFAULT NULL.
  • PostgreSQL has a boolean data type.

Upvotes: 2

Related Questions