Create an automatically increasing primary key column in PostgreSQL

Question

I am following the examples in CREATE TABLE:

CREATE TABLE distributors (
     did    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
     name   varchar(40) NOT NULL CHECK (name <> '')
);

However, it gives me ERROR: syntax error at or near "GENERATED". Why is that and how should I fix it?

\! psql -V returns psql (PostgreSQL) 10.5 (Ubuntu 10.5-1.pgdg14.04+1)
SELECT version(); returns PostgreSQL 9.4.19 on x86_64-pc-linux-gnu (Ubuntu 9.4.19-1.pgdg14.04+1), compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.4) 4.8.4, 64-bit

Edits:

Thanks to @muistooshort, I checked the 9.4 docs. So I execute:

CREATE TABLE distributors (
     did    integer PRIMARY KEY DEFAULT nextval('serial'),
     name   varchar(40) NOT NULL CHECK (name <> '')
);

Nevertheless, it now gives me ERROR: relation "serial" does not exist...

psql is from PostgreSQL 10.5 but what version of PostgreSQL is the server running? — mu is too short
@muistooshort SELECT version(); gives me PostgreSQL 9.4.19 on x86_64-pc-linux-gnu (Ubuntu 9.4.19-1.pgdg14.04+1), compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.4) 4.8.4, 64-bit. Does it mean that I should read documentation for version 9.4 instead of 10? — ytu
Yes, check the 9.4 docs. AFAIK IDENTITY was added in version 10, you'll want to use serial with 9.4. — mu is too short
@muistooshort I just tried that but failed again. Would you mind checking my edits? Should I create relation serial just to create distributors? — ytu
I need a bit more space than a comment provides so I added an answer. — mu is too short

mu is too short mu is too short · Accepted Answer · 2018-08-15T04:24:10

The SQL standard IDENTITY was added in PostgreSQL 10 but your server (which does all the real work) is 9.4. Before 10 you have to use serial or bigserial types:

CREATE TABLE distributors (
     did    serial not null primary key,
     name   varchar(40) NOT NULL CHECK (name <> '')
);

The serial type will create a sequence to supply values, attach the sequence to the table, and hook up a default value for did to get values from the sequence.

Create an automatically increasing primary key column in PostgreSQL

1 Answers