PostgreSQL allows us to create sequence objects, otherwise known as “sequence generators”, or simply “sequences”. As the name suggests, a sequence object is used to generate sequence numbers.

We can use sequences to generate incrementing numbers or decrementing numbers.

Syntax

The syntax for creating a sequence generator goes like this:

CREATE [ { TEMPORARY | TEMP } | UNLOGGED ] SEQUENCE [ IF NOT EXISTS ] name
    [ AS data_type ]
    [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
    [ OWNED BY { table_name.column_name | NONE } ]

Example

Here’s an example of creating a sequence in PostgreSQL:

CREATE SEQUENCE Sequence1;

This is all that’s required to create a sequence object. We specify CREATE SEQUENCE, followed by the name we want to give to the sequence.

That creates a sequence with the default options.

Specify Options for the Sequence

We can also specify our own custom options for the sequence.

Here’s an example:

CREATE SEQUENCE Sequence2
    AS integer
    INCREMENT 10
    MINVALUE 100
    MAXVALUE 250000
    START 101
    CACHE 1
    NO CYCLE;

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.

Data Type

In the previous example I specified the data type as integer. We have the option of smallint, integer, and bigint.

The default data type is bigint. In other words, if we don’t specify the data type, the sequence will use bigint. This has implications for the default MAXVALUE and MINVALUE. The default maximum value for an ascending sequence is the maximum value of the data type. The default minimum value for a descending sequence is the minimum value of the data type.

Let’s create some more sequences:

CREATE SEQUENCE Sequence3 AS integer
    INCREMENT 1;
CREATE SEQUENCE Sequence4 AS integer
    INCREMENT -1;

In this case, both sequences use the integer type, but they use a different increment. Specifically, Sequence3 uses a positive increment, while Sequence4 uses a negative increment.

Next we’ll see how this affects the MINVALUE and MAXVALUE for each sequence.

Check the Sequences

We can run the following query to get information about our sequences:

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.relname LIKE 'sequence%';

Result:

  relname  | seqstart | seqincrement |       seqmax        |   seqmin    | seqcache | seqcycle 
-----------+----------+--------------+---------------------+-------------+----------+----------
 sequence1 |        1 |            1 | 9223372036854775807 |           1 |        1 | f
 sequence2 |      101 |           10 |              250000 |         100 |        1 | f
 sequence3 |        1 |            1 |          2147483647 |           1 |        1 | f
 sequence4 |       -1 |           -1 |                  -1 | -2147483648 |        1 | f

We can see the differences between each of the sequences. In particular, we can see how sequence3 and sequence4 differ, based on the increments that we gave them in the previous example (i.e. one has a positive increment, the other has a negative increment).

Regarding sequence1, the seqmax (maximum value) reflects the bigint type. That’s because we didn’t specify a data type for that sequence, and so it used the default type (which is bigint). For sequence2, we specified our own MAXVALUE of 250000, and so this is reflected in the seqmax column. We can also see that some of its other properties reflect the values that we gave the sequence when we created it.