postgresql ERROR: syntax error at or near “PRIMARY”

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

Answer

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.