postgresql ERROR: syntax error at or near "PRIMARY"

Question

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
);

user330315 · Accepted Answer · 2020-02-28 21:36:02Z

4

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.

answered Feb 28, 2020 at 21:36

user330315

Sign up to request clarification or add additional context in comments.

Comments

GMB · Accepted Answer · 2020-02-28 21:38:11Z

2

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

answered Feb 28, 2020 at 21:38

GMB

224k25 gold badges103 silver badges151 bronze badges

Collectives™ on Stack Overflow

postgresql ERROR: syntax error at or near "PRIMARY"

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related