PostgreSQL SEQUENCE

PostgreSQL SEQUENCE is used to generate an ordered sequence of numbers. It is called a sequence, sequence object or sequence generator.

You can create a sequence with the CREATE SEQUENCE statement and delete a sequence with DROP SEQUENCE.

SERIAL Both columns and identity columns use sequences internally.

PostgreSQL CREATE SEQUENCE syntax

To create a sequence in PostgreSQL, use the CREATE SEQUENCE statement as follows:

CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] seq_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 } ]

Explanation:

TEMPORARY | TEMP

Specifies that the current sequence is a temporary sequence. You can only use a temporary sequence in the current session. It is automatically dropped when the session ends. It is optional.

IF NOT EXISTS

If specified, no error will be raised if a sequence with the same name exists.

seq_name

The name of the sequence to create.

data_type

It indicates the data type of this sequence. Available values: smallint, integer, and bigint, and bigint is the default values.

INCREMENT [ BY ] increment

The stride of the sequence growth, which can be an integer or a negative number. Positive values ​​will produce an ascending sequence, and negative values ​​will produce a descending sequence. The default is the value 1.

MINVALUE minvalue | NO MINVALUE

The minvalue is the minimum value of the sequence. NO MINVALUE means to use the minimum value of the data type. The default value for ascending sequence is 1, and the default value for descending sequence is the minimum value of the data type.

MAXVALUE maxvalue | NO MAXVALUE

The maxvalue is the maximum value of the sequence. NO MAXVALUE means to use the maximum value of the data type. The default value for descending sequence is 1, and the default value for ascending sequence is the maximum value of the data type.

START [ WITH ] start

The start is the starting value for this sequence. The default for ascending sequences is minvalue, and the default for descending sequences is maxvalue.

CACHE cache

The cache is the number of sequence values ​​cached in memory so that they can be accessed quickly. The default value is 1, which is also the smallest acceptable value.

[ NO ] CYCLE

It indicates whether the sequence is loopable. If NO CYCLE specified, then when the sequence reaches the end, an error will occur when the value is retrieved.

OWNED BY { table_name.column_name | NONE }

Associates this sequence with the columns of the specified table. If this column is dropped, the sequence is automatically dropped.

PostgreSQL DROP SEQUENCE syntax

To drop one or more sequences in PostgreSQL, use the DROP SEQUENCE statement:

DROP SEQUENCE [ IF EXISTS ] seq_name [, ...]
[ CASCADE | RESTRICT ];

Explanation:

IF EXISTS

Does not throw an error if the specified sequence does not exist.

seq_name

The name of the sequence to drop.

CASCADE

Objects that depend on this sequence are automatically dropped, and objects that depend on them are dropped in turn.

RESTRICT

Refuse to drop this sequence if any object depends on it. This is the default behavior.

Access PostgreSQL sequences

You can manipulate PostgreSQL sequences using the following three functions:

• nextval: Gets and returns the next value of the specified sequence.
• currval: Returns the current value of the specified sequence in the current session.
• setval: Reset the current value of the specified sequence.

You can also view a specified sequence with the following statement:

SELECT * FROM seq_name;

PostgreSQL sequence Examples

The following statement creates an ascending sequence starting at 10 with steps of 10:

CREATE SEQUENCE asc_seq
INCREMENT 10
START 10;

Use the following nextval() function to get the next value from the sequence:

SELECT nextval('asc_seq');

nextval
---------
     10
(1 row)

Use the following nextval() function to get the next value from the sequence:

SELECT nextval('asc_seq');

nextval
---------
     20
(1 row)

Use the currval() function to get the current value of the sequence:

SELECT currval('asc_seq');

currval
---------
     20
(1 row)

Use the setval() function to set the current value of a sequence:

SELECT setval('asc_seq', 50);

setval
--------
    50
(1 row)

Use the following nextval() function to get the next value from the sequence:

SELECT nextval('asc_seq');

nextval
---------
     60
(1 row)

To view this sequence, use the following SELECT statement:

SELECT * FROM asc_seq;

last_value | log_cnt | is_called
------------+---------+-----------
        60 |      32 | t
(1 row)

Conclusion

In PostgreSQL, you can use the CREATE SEQUENCE statement to create a sequence and use to DROP SEQUENCE delete a sequence. You can use these three functions nextval, currval, and setval to access a sequence.