In PostgreSQL we can create auto-incrementing columns using the serial data type. The serial type causes the column to be automatically populated with an auto-incrementing value each time a new row is inserted. The same applies for the smallserial and bigserial types.

This article provides an overview of how these data types work.

Create a SERIAL Column

Here’s an example of creating a serial column in Postgres:

CREATE TABLE Idiots (
    IdiotId serial PRIMARY KEY,
    IdiotName VARCHAR
);

All I did was specify serial for the column’s data type.

Alternatively, I could have specified smallserial for a smaller range of values, or bigserial for a larger range. These types work just like serial columns, the only difference is in their range. So, whenever I mention serial columns in this article, the same concept applies to smallserial and bigserial, unless I’m referring to the range.

Behind the scenes, a NOT NULL constraint is applied to the column. This ensures that a NULL value cannot be inserted. In most cases we will also want to apply a UNIQUE or PRIMARY KEY constraint to the column. In my case I applied a PRIMARY KEY.

Use a SERIAL Column

Whenever we insert a new row without specifying a value for the serial column, that column will be automatically populated with an auto-incrementing value.

Example:

INSERT INTO Idiots ( IdiotName ) 
VALUES ( 'Dumb' ), ( 'Dumber' ), ( 'Dumbest' )
RETURNING *;

Result:

 idiotid | idiotname 
---------+-----------
       1 | Dumb
       2 | Dumber
       3 | Dumbest

The RETURNING clause allows us to see the values that were inserted.

In this case I didn’t specify a value for the IdiotId column and so it automatically generated one for me.

Override a SERIAL Column

Here’s what happens when I insert my own value into the serial column:

INSERT INTO Idiots ( IdiotId, IdiotName ) 
VALUES ( 20, 'Homer' )
RETURNING *;

Result:

 idiotid | idiotname 
---------+-----------
      20 | Homer

It inserted the value that I specified.

So, the serial column only generated a value when I didn’t supply one.

Let’s take a look at the table:

SELECT * FROM Idiots;

Result:

 idiotid | idiotname 
---------+-----------
       1 | Dumb
       2 | Dumber
       3 | Dumbest
      20 | Homer

As expected, the first three rows contain the automatically generated numbers in the IdiotId column, and the last row contains the value that we specified.

Behind the Scenes

The serial type is not actually a true data type. The same goes for smallserial and bigserial.

As the PostgreSQL documentation states:

The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns.

Despite the fact that it’s not a true type, when we create a serial column, we don’t need to specify any other data type.

The serial type is created as an integer type (smallserial is a smallint, and bigserial is a bigint), and an implicit sequence is created behind the scenes. This sequence is what’s used to populate the column’s value. It starts off with a starting value, then auto-increments with each row that’s inserted.

We can use the information_schema.sequences view to look at the sequence:

SELECT 
    sequence_name,
    data_type,
    start_value,
    minimum_value,
    maximum_value,
    increment,
    cycle_option
FROM information_schema.sequences;

Result:

   sequence_name    | data_type | start_value | minimum_value | maximum_value | increment | cycle_option 
--------------------+-----------+-------------+---------------+---------------+-----------+--------------
 cats_catid_seq     | integer   | 1           | 1             | 2147483647    | 1         | NO
 idiots_idiotid_seq | integer   | 1           | 1             | 2147483647    | 1         | NO

In my case, there are two sequences listed. The first sequence is from another table called Cats, and was also implicitly created when I used serial for the CatId column. The second one is the one for the Idiots.IdiotId column that was created with the above CREATE TABLE statement.

The name of the sequence created for a serial column is derived from the table name and column name. The above code created a sequence called idiots_idiotid_seq.

The serial2, serial4 and serial8 Types

It’s also possible to specify serial2, serial4, and serial8 for the column. These correspond to the different serial types. Specifically:

Type Name	Creates the Following Data Type
serial2 smallserial	smallint
serial4 serial	integer
serial8 bigserial	bigint

An Alternative – The IDENTITY Column

As of PostgreSQL 10, we can create identity columns. These work in much the same way that serial columns work, with a few exceptions. The main difference is that identity columns adhere to the SQL standard.

To create the above table with an identity column, we would do something like this:

CREATE TABLE Idiots (
    IdiotId int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    IdiotName VARCHAR
);

It’s a bit more verbose, but we do get some options over how we want the identity column to behave.

See Create an IDENTITY Column in PostgreSQL for more information on identity columns and an example of creating one.