When we create an IDENTITY column in PostgreSQL, we have the option of specifying our own values for the sequence object that’s created.

This article provides an example of creating an IDENTITY column with our own sequence options.

Background

Each time we create an IDENTITY column, an implicit sequence object is created. If we don’t specify any options for the sequence, it uses the default options.

However, the syntax for the IDENTITY column allows us to specify the same options that we can specify when creating a sequence on its own.

Example

Below is an example of specifying sequence options when creating an IDENTITY column:

CREATE TABLE Idiots (
    IdiotId int GENERATED BY DEFAULT AS IDENTITY (
    INCREMENT 10
    MINVALUE 100
    MAXVALUE 250000
    START 101
    CACHE 10
    NO CYCLE
    )
    PRIMARY KEY,
    IdiotName VARCHAR
);

The sequence options are the part within the inner set of parentheses.

The GENERATED BY DEFAULT AS IDENTITY part is what creates the actual IDENTITY column. If we wanted to use the default options for the sequence, we could leave it at that. But in this case, we wanted to specify our own custom options for the sequence, and so that’s what follows in the parentheses.

In this case, I specified the INCREMENT, MINVALUE, MAXVALUE, START, and NO CYCLE options for the sequence.

Here’s what those sequence options do:

INCREMENT	Specifies which value is added to the current sequence value to create a new value. A positive value makes it an ascending sequence, a negative one a descending sequence. The default value is 1.
MINVALUE	Specifies the minimum value a sequence can generate. If this clause is not supplied or NO MINVALUE is specified, then default values are used. The default for an ascending sequence is 1. The default for a descending sequence is the minimum value of the data type.
MAXVALUE	Specifies the maximum value for the sequence. If this clause is not supplied or NO MAXVALUE is specified, then default values are used. The default for an ascending sequence is the maximum value of the data type. The default for a descending sequence is -1.
START	Specifies the value to start the sequence at. The default value for ascending sequences is the MINVALUE amount, and the default for descending sequences is the MAXVALUE amount.
CACHE	Specifies how many sequence numbers are to be preallocated and stored in memory for faster access. The default value is 1, which is also the minimum value.
CYCLE NO CYCLE	We can use CYCLE or NO CYCLE to specify whether or not the sequence will start again when it reaches its MINVALUE or MAXVALUE amount (depending on whether it’s an ascending sequence or descending one). If NO CYCLE is used, an error will occur once the sequence tries to generate a value that exceeds its MINVALUE/MAXVALUE.

Check the Sequence

We can run the following query to check the sequence options:

SELECT 
    pc.relname,
    ps.seqstart,
    ps.seqincrement,
    ps.seqmax,
    ps.seqmin,
    ps.seqcache,
    ps.seqcycle
FROM pg_class pc
JOIN pg_sequence ps 
ON pc.oid = ps.seqrelid
WHERE pc.relkind = 'S' AND pc.relname LIKE '%idiot%';

Result:

      relname       | seqstart | seqincrement | seqmax | seqmin | seqcache | seqcycle 
--------------------+----------+--------------+--------+--------+----------+----------
 idiots_idiotid_seq |      101 |           10 | 250000 |    100 |       10 | f