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...

Answer 1

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.