Basic syntax for CREATE TABLE in PostgreSQL

Question

I am working with PostgreSQL for the first time. I have this statement:

CREATE TABLE IF NOT EXISTS sequences (
  SEQU_NK   SERIAL PRIMARY KEY NOT NULL UNIQUE,
  NOM_SEQU varchar(30) NOT NULL,
  PROCHAIN bigint NOT NULL,
  PRIMARY KEY (SEQU_NK),
  UNIQUE KEY IDX_SEQU_NOM (NOM_SEQU)
)

When I run it I get:

ERROR:  syntax error at or near "KEY"
LINE 6:   UNIQUE KEY IDX_SEQU_NOM (NOM_SEQU)
                 ^
********** Error **********

ERROR: syntax error at or near "KEY"

http://www.postgresql.org/docs/current/static/sql-createtable.html — , Aug 27 '15 at 12:28

score 1 · Accepted Answer · answered Aug 27 '15 at 12:20

1

Try like this:

CREATE TABLE IF NOT EXISTS sequences (
  SEQU_NK   SERIAL PRIMARY KEY NOT NULL UNIQUE,
  NOM_SEQU varchar(30) NOT NULL,
  PROCHAIN bigint NOT NULL,
  CONSTRAINT "IDX_SEQU_NOM" UNIQUE (NOM_SEQU)
)

SQLFIDDLE DEMO

answered Aug 27 '15 at 12:20

Rahul Tripathi

168,305
31
280
331

Please explain a bit ? – xav xav Aug 27 '15 at 12:21
@xavxav:- There are two problems in your query. 1) You have defined the Primary key twice which is not correct. (`PRIMARY KEY (SEQU_NK)` is not required) 2) The syntax to create the UNIQUE constraint was not correct. Hope this helps :) – Rahul Tripathi Aug 27 '15 at 12:22
Thanks Rahul..One more question: Can we write comments in PostGre like this : "PASSE varchar(40) COLLATE latin1_general_ci NOT NULL COMMENT 'md5'," – xav xav Aug 27 '15 at 12:26
1

@xavxav: it's either Postgres or PostgreSQL but never "postgre". Regarding your question to specify comments: see [the manual](http://www.postgresql.org/docs/current/static/sql-comment.html) or [this question](http://stackoverflow.com/q/32070876/330315) – Aug 27 '15 at 12:30
@a_horse_with_no_name thanks... for the links... But could you please answer : Can we write comments in PostGre like this : "PASSE varchar(40) COLLATE latin1_general_ci NOT NULL COMMENT 'md5'," – xav xav Aug 27 '15 at 12:40
@xavxav: please read the manual and the other answer. Both sources will answer that question. – Aug 27 '15 at 12:44
@a_horse_with_no_name My tery after reading it is: "PASSE varchar(40) COLLATE latin1_general_ci NOT NULL COMMENT ON utilisateurs IS 'md5'," which again gives error "ERROR: syntax error at or near "COMMENT" LINE 5: PASSE varchar(40) COLLATE latin1_general_ci COMMENT ON ut..." – xav xav Aug 27 '15 at 12:49
1

@xavxav: Please start a new ***question*** for your new question. Comments are not the place. – Erwin Brandstetter Aug 27 '15 at 13:45

score 0 · Answer 2 · answered Aug 27 '15 at 13:59

CREATE TABLE sequences (
  sequ_nk  serial PRIMARY KEY,
  nom_sequ varchar(30) NOT NULL UNIQUE,
  prochain bigint NOT NULL
);

It's UNIQUE , not ~~UNIQUE KEY~~.
There can only be one PRIMARY KEY.
A PRIMARY KEY column is UNIQUE and NOT NULL automatically.
Read the manual.
Or, better yet, read the manual for your actual Postgres version 8.4 - which tells you that ~~IF NOT EXISTS~~ is not yet implemented in version 8.4 (wait for 9.1).
Or, better still, upgrade to a current version of Postgres. Version 8.4 has reached EOL last year.
Aside: Don't use quoted upper-case identifiers, or you have to keep quoting them, always.

Basic syntax for CREATE TABLE in PostgreSQL

2 Answers2