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
– mu is too short, Commented Aug 15, 2018 at 3:49
@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
– ytu, Commented Aug 15, 2018 at 3:55
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
– mu is too short, Commented Aug 15, 2018 at 3:57
@muistooshort I just tried that but failed again. Would you mind checking my edits? Should I create relation serial just to create distributors? — ytu
– ytu, Commented Aug 15, 2018 at 4:14
I need a bit more space than a comment provides so I added an answer. — mu is too short
– mu is too short, Commented Aug 15, 2018 at 4:24

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

9

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.

answered Aug 15, 2018 at 4:24

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

4 Comments

ytu Over a year ago

Thanks, that works! Just out of curiosity, why doesn't the example provided in the 9.4 docs work then?

mu is too short Over a year ago

Maybe you're reading the docs wrong. serial so it replaces integer, nextval takes a sequence name as its argument but the serial pseudo-type supplies the default value for you.

ytu Over a year ago

I figured it may be that way, but I literally copied and pasted the example in that doc...

mu is too short Over a year ago

The example is incomplete, sometimes documentation leaves things out to avoid confusing what they're trying to show you.

Collectives™ on Stack Overflow

Create an automatically increasing primary key column in PostgreSQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related