Using a generated key is something DBAs often do, mainly for performance reasons. In an ideal world, we would rely on the table natural primary key and we’ll be over. However, using an artificial primary key has been proven beneficial for performance.

In this blog post, I won’t explain what’s a primary key or what’s a natural or artificial key. If you’re interested in database design, you’ll find great books out there on the matter. If you think database design is some ancient thing that shouldn’t be done anymore, you might be interested in Doctor Stonebraker’s blog post here: https://www.enterprisedb.com/blog/schema-later-considered-harmful

In this blog post, I will explain 3 ways to have an auto-increment with Postgres.

Sequences

The first obvious way to have an auto-increment number is to use sequences. (Actually, we’ll see later that the other ways also rely on sequences).

So here is how to use simply a sequence:

laetitia=# create table test(id integeri primary key, value text);
CREATE TABLE
laetitia=# create sequence my_seq;
CREATE SEQUENCE
laetitia=# insert into test (select nextval('my_seq'), 'blabla');
INSERT 0 1
laetitia=# select * from test;
 id | value
----+--------
  1 | blabla
(1 row)

But, we can do better. What if we wanted the id column to be automatically filled with the sequence next number?

laetitia=# create sequence my_seq;
CREATE SEQUENCE
laetitia=# create table test (id integer default nextval('my_seq') primary key, value text);
CREATE TABLE
laetitia=# insert into test(value) values ('blabla');
INSERT 0 1
laetitia=# select * from test;
 id | value
----+--------
  1 | blabla
(1 row)

So, that was the first way to have auto-incremented ids with Postgres.

The serial datatype

Since Postgres 8.2 (released in 2006), Postgres added serial data types. As Postgres documentation states, it will do the same thing we did before but you will do that with only one word. (see https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL.

laetitia=# create table test (id serial primary key, value text);
CREATE TABLE
laetitia=# insert into test (value) values ('blabla');
INSERT 0 1
laetitia=# select * from test;
 id | value
----+--------
  1 | blabla
(1 row)

If you look at the table, you’ll find out a sequence has been created and is used as the default value for the id column:

laetitia=# \d test
                            Table "public.test"
 Column |  Type   | Collation | Nullable |             Default              
--------+---------+-----------+----------+----------------------------------
 id     | integer |           | not null | nextval('test_id_seq'::regclass)
 value  | text    |           |          | 
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)
laetitia=# \ds
             List of relations
 Schema |    Name     |   Type   |  Owner   
--------+-------------+----------+----------
 public | test_id_seq | sequence | laetitia
(1 row)

The identity column

Sequences are standard SQL compliant. However, the serial data type is not. But SQL has a way to create auto-increment for a column that does not involve creating a sequence explicitly. It’s the generated as identity.

There are two flavors of that syntax, depending on what you want to do.

The first way to use this syntax is when you want to use the sequence value as the default, but allow to enter another value manually. In that case, the syntax is generated by default as identity. But this won’t prevent someone to insert a value without using the sequence:

laetitia=# create table test (id integer generated by default as identity primary key, value text);
CREATE TABLE
laetitia=# \d test
                            Table "public.test"
 Column |  Type   | Collation | Nullable |             Default              
--------+---------+-----------+----------+----------------------------------
 id     | integer |           | not null | generated by default as identity
 value  | text    |           |          | 
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

laetitia=# insert into test (value) values ('blabla');
INSERT 0 1
laetitia=# select * from test;
 id | value  
----+--------
  1 | blabla
(1 row)

laetitia=# insert into test (id, value) values (2,'blabla');
INSERT 0 1
laetitia=# select * from test;
 id | value  
----+--------
  1 | blabla
  2 | blabla
(2 rows)

laetitia=# insert into test (value) values ('blabla');
ERROR:  duplicate key value violates unique constraint "test_pkey"
DETAIL:  Key (id)=(2) already exists.

As you see, you can add a value manually, and then the insert order will error due to a duplicate key violation because the sequence is lagging behind the last number in the id column.

To prevent that to happen, we’ll create a constraint that will prevent anyone to insert manually data into that column. that’s the other generated as identity syntax.

laetitia=# create table test (id integer generated always as identity primary key, value text);
CREATE TABLE
laetitia=# \d test
                          Table "public.test"
 Column |  Type   | Collation | Nullable |           Default            
--------+---------+-----------+----------+------------------------------
 id     | integer |           | not null | generated always as identity
 value  | text    |           |          | 
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

laetitia=# insert into test (value) values ('blabla');
INSERT 0 1
laetitia=# insert into test (id, value) values (2,'blabla');
ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

We can still check that it’s using a sequence under the hood:

laetitia=# \ds
Did not find any relations.
laetitia=# create table test (id integer generated always as identity primary key, value text);
CREATE TABLE
laetitia=# \ds
             List of relations
 Schema |    Name     |   Type   |  Owner   
--------+-------------+----------+----------
 public | test_id_seq | sequence | laetitia
(1 row)

To sum up, there are several ways to create auto-increment values for a column. I’d suggest using almost all the time the generated always as identity as this syntax will add a constraint to prevent any sequence desynchronization, due to manual inserts. Of course, should someone want to mess with the sequence behind the identity column, with the appropriate permission, they will find a way to do that.