What is the acceptable method for generating an auto incrementing primary key in PostgreSQL?

Question

Is there no easy way to do this without sequences and triggers? I have average SQL skills, and I want to use the industry standard method for pl/sql (PostgreSQL). I'm basically converting over this example table from Spring Security:

create table group_members (
    id bigint generated by default as identity(start with 0) primary key,
    username varchar(50) not null,
    group_id bigint not null,
    constraint fk_group_members_group foreign key(group_id) references groups(id));

What I have so far:

CREATE TABLE auth_group_members (
    id NUMBER,
    username VARCHAR(50) NOT NULL,
    group_id NUMBER NOT NULL,
    CONSTRAINT "FK_AuthGroupMembers" FOREIGN KEY(group_id) REFERENCES auth_groups(id)
);

mu is too short · Accepted Answer · 2011-05-13T23:04:20.287

13

The standard way would be to use serial or bigserial:

The data types serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases).
[...]
Thus, we have created an integer column and arranged for its default values to be assigned from a sequence generator.

So you'd create the table with something like this:

CREATE TABLE auth_group_members (
    id bigserial primary key,
    username VARCHAR(50) NOT NULL,
    group_id NUMBER NOT NULL,
    CONSTRAINT "FK_AuthGroupMembers" FOREIGN KEY(group_id) REFERENCES auth_groups(id)
);

The serial and bigserial types do create a sequences behind the scenes but you never have to work with the sequence directly.

edited May 13 '11 at 23:04

answered May 13 '11 at 22:36

mu is too short

426,620
70
833
800

2

The original table said `id bigint ...` so the better equivalent would be `bigserial`. – cjm May 13 '11 at 22:51
I thought it might be serial based upon what I was seeing in pgadmin, but every time I searched for serial, or plsql keywords in Google I would get a bunch of links for cracked software :( So a big thanks to you! – May 14 '11 at 00:50
2

@hal100001: You might want to bookmark the [PostgreSQL documentation](http://www.postgresql.org/docs/manuals/), it is very good and even includes an index. – mu is too short May 14 '11 at 03:32

score 3 · Answer 2 · answered Apr 15 '18 at 21:10

In PostgreSQL 10 you can use identity columns. Here is example:

create table group_members (
    id bigint generated by default as identity(start with 1) primary key,
    username varchar(50) not null,
    group_id bigint not null
    );

Additionally:

Good article about identity columns vs serial.
PostgreSQL documentation for more info (Ctrl+F and search "AS IDENTITY").

What is the acceptable method for generating an auto incrementing primary key in PostgreSQL?

2 Answers2