How to create an auto increment primary key field in PostgreSQL

By | 13 February, 2014

So, you want to create a fancy cool table in PostgreSQL, filled with great fields of incomparable values, along with a nice auto increment primary key.

You do something like this at table creation time:

CREATE TABLE mysupertable
(
    id_super    bigserial primary key,
    field2      VARCHAR(40) not null
);

Or at table..err..alteration time (any time, really):

ALTER TABLE mysupertable 
ADD COLUMN id_super BIGSERIAL PRIMARY KEY;

The key (no pun intended) to all this is in that bigserial datatype, that automatically creates a sequence. Cool and simple, eh?

Now go and auto increment stuff.

More to read:

PostgreSQl documentation Numeric Types