postgresql ERROR: syntax error at or near "PRIMARY"

user_9891471 0
Viewed 6487

I'm new to PostgreSQL. I've been trying to create tables in a database but some queries give weird errors. the first snippet throws a syntax error but when I add "serial" on the problematic column the error is resolved. Should all primary keys auto-increment in PostgreSQL? 

CREATE TABLE songplays(
   songplay_id PRIMARY KEY, --This does not work, it throws syntax error at or near "PRIMARY"
   start_time VARCHAR (50) NOT NULL,
   user_id VARCHAR (50) NOT NULL,
   level VARCHAR (355) UNIQUE NOT NULL,
   song_id VARCHAR (50) NOT NULL NOT NULL,
   artist_id VARCHAR (50) NOT NULL NOT NULL,
   session_id VARCHAR (50) NOT NULL NOT NULL,
   location VARCHAR (50) NOT NULL NOT NULL,
   user_agent VARCHAR (50) NOT NULL NOT NULL
);

This works added serial constraint on songplay_id

CREATE TABLE songplays(
   songplay_id serial PRIMARY KEY,
   start_time VARCHAR (50) NOT NULL,
   user_id VARCHAR (50) NOT NULL,
   level VARCHAR (355) UNIQUE NOT NULL,
   song_id VARCHAR (50) NOT NULL NOT NULL,
   artist_id VARCHAR (50) NOT NULL NOT NULL,
   session_id VARCHAR (50) NOT NULL NOT NULL,
   location VARCHAR (50) NOT NULL NOT NULL,
   user_agent VARCHAR (50) NOT NULL NOT NULL
);
create-table ddl constraints postgresql sql
2 Answers

Should all primary keys auto-increment in PostgreSQL?

No, not necessarily.

In the first statement you are not specifying a data type for the column, if you don't want it to be an auto increment, simply use integer:

 songplay_id integer PRIMARY KEY,

Note that serial is not a "constraint", it's a data type definition which is a shortcut for an integer column that takes its default value from a sequence.

user_3303150

Postgres create table syntax is:

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option ... ] }
    [, ... ]
] )

Every column definition starts with a column name, followed by a data type. The first column in your first statement fails to that:

songplay_id PRIMARY KEY

If not using special datatype SERIAL, you still have to provide a data type, for example:

 songplay_id INTEGER PRIMARY KEY
user_106767160
Related