How to translate the following query to postgresql

Question

CREATE TABLE IF NOT EXISTS `p_reqstats` (
  `sdate` int(10) unsigned NOT NULL,
  `deptID` int(10) unsigned NOT NULL,
  `opID` int(10) unsigned NOT NULL,
  `requests` int(10) NOT NULL,
  `taken` smallint(5) unsigned NOT NULL,
  `declined` smallint(5) unsigned NOT NULL,
  `message` smallint(5) unsigned NOT NULL,
  `initiated` smallint(5) unsigned NOT NULL,
  `initiated_taken` smallint(5) unsigned NOT NULL,
  `rateit` smallint(5) unsigned NOT NULL,
  `ratings` smallint(5) unsigned NOT NULL,
  PRIMARY KEY (`sdate`,`deptID`,`opID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I wonder in fact part :

 PRIMARY KEY (sdate,deptID,opID)

Here the problem occurs because the

What version of postgresql? What have you tried so far, where are you stuck? — Bjoern, Oct 26 '11 at 07:31

score 3 · Answer 1 · answered Oct 26 '11 at 08:31

A few issues:

You won't have IF NOT EXISTS unless you're using 9.1 or higher.
PostgreSQL doesn't know what int(10) means, you just want int.
PostgreSQL doesn't know what unsigned means.
Backticks for quoting identifiers is a MySQLism, most databases use double quotes (the standard).
smallint is supported but int is recommended unless you are tight on disk space.

So something like this should work:

create table "p_reqstats" (
    "sdate" int NOT NULL,
    "deptID" int NOT NULL,
    "opID" int NOT NULL,
    "requests" int NOT NULL,
    "taken" smallint NOT NULL,
    "declined" smallint NOT NULL,
    "message" smallint NOT NULL,
    "initiated" smallint NOT NULL,
    "initiated_taken" smallint NOT NULL,
    "rateit" smallint NOT NULL,
    "ratings" smallint NOT NULL,
    PRIMARY KEY ("sdate","deptID","opID")
);

If you needed the extra space that an unsigned value would give you then you could use bigint instead of int.

How to translate the following query to postgresql

1 Answers1