Sequences

Eugenia Kuzina

The sequence is the schema object that generates a sequence of unique integers in ascending or descending order. The sequence is not associated with any table, but it can be used to populate data in the primary key or unique columns of a table. The PostgreSQL serial types are sequences.

Create a sequence

To create a sequence, use the CREATE SEQUENCE command that defines a new sequence generator.

CREATE [ TEMPORARY | TEMP ] 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 } ]
Parameters of the CREATE SEQUENCE command

TEMPORARY | TEMP

If specified, the sequence object is created only for the current session. A temporary sequence is dropped at the end of the session. Existing permanent sequences with the same name are not visible in the current session while the temporary sequence exists. It is only possible to reference a permanent sequence with a schema-qualified name

IF NOT EXISTS

PostgreSQL creates a sequence if a relation with the same name does not exist. When this option is specified, PostgreSQL does not throw an error if a relation with the specified name is in the database. There is no guarantee that the existing relation matches the one that would be created

<name>

The name of the new sequence. Can be schema-qualified (<schema_name>.<sequence_name>)

<data_type>

The optional clause AS <data_type> specifies the data type of the sequence. Valid types are smallint, integer, and bigint. The default value is bigint. The data type determines the default minimum and maximum values of the sequence

<increment>

The optional clause INCREMENT [BY] <increment> specifies the value that is added to the current sequence value to create a new value. A positive value sets an ascending sequence, a negative one specifies a descending sequence. The default value is 1

MINVALUE <minvalue> | NO MINVALUE

The optional clause MINVALUE <minvalue> determines the minimum value that a sequence can generate. If this clause is not set or NO MINVALUE is specified, defaults are used. The default value for an ascending sequence is 1. The default value for a descending sequence is the minimum value of the data type

MAXVALUE <maxvalue> | NO MAXVALUE

The optional clause MAXVALUE <maxvalue> determines the maximum value for the sequence. If this clause is not specified or NO MAXVALUE is specified, then default values will be used. The default value for an ascending sequence is the maximum value of the data type. The default value for a descending sequence is -1

<start>

The optional clause START WITH <start> specifies the value at which to start the sequence. The default start value is minvalue for ascending sequences and maxvalue for descending ones

<cache>

The optional clause CACHE <cache> specifies how many sequence values should be retrieved and stored in memory for faster access. The minimum value is 1 — only one value can be generated at a time (no cache), 1 is also the default value

[ NO ] CYCLE

The CYCLE option allows the sequence to wrap around when maxvalue or minvalue is reached by an ascending or descending sequence, respectively. If the limit is reached, the next generated number will be the minvalue for an ascending sequence or maxvalue for a descending sequence.

If the optional NO CYCLE key word is specified, any nextval calls after the sequence reaches its maximum value will return an error. NO CYCLE is used by default

OWNED BY table_name.column_name | NONE

The OWNED BY option causes the sequence to be associated with a table column. If this column or its whole table is dropped, the sequence will be automatically dropped. The specified table must have the same owner and be in the same schema as the sequence. OWNED BY NONE specifies that there is no such association and is used by default

The following example creates a sequence starting with 2:

CREATE SEQUENCE mysequence START 2;

CREATE SEQUENCE creates and initializes a new special single-row table with the specified name. The user who executes CREATE SEQUENCE becomes the owner of the sequence.

If a schema name is given, the sequence is created in the specified schema. Otherwise, it is created in the current schema. Temporary sequences exist in a special schema, so a schema name cannot be given when creating a temporary sequence.

Sequences are based on bigint arithmetic, the range cannot exceed the range of an eight-byte integer (from -9223372036854775808 to 9223372036854775807).

To examine the current state of a sequence, use SELECT.

SELECT * FROM mysequence;

The result:

 last_value | log_cnt | is_called
------------+---------+-----------
          2 |       0 | f

The last_value field of the sequence shows the last value allocated by any session. This value might be obsolete by the time it is printed if other sessions are actively doing nextval calls.

PostgreSQL does not log (write to WAL) each fetching of a value from a sequence for performance reasons, it pre-logs a few fetches in advance. If PostgreSQL crashes, it loses (skips over) as many values as pre-logged. log_cnt shows how many fetches remain before a new WAL record has to be written.

If the is_called field is t — true, the next nextval call will advance the sequence before returning a value. If is_called is f — false, nextval will return the last_value value.

After a sequence is created, use the sequence manipulation functions to operate with the sequence. See Work with sequences.

If the cache value greater than 1 is used for a sequence object, it is used concurrently by multiple sessions. Each session allocates and caches successive sequence values during one access to the sequence object and increases the sequence’s last value accordingly. Then, the next cache-1 calls of nextval within this session return the preallocated values without requesting the sequence object. Any numbers allocated but not used within a session will be lost when that session terminates. It results in missing values in the sequence.

Although multiple sessions are guaranteed to allocate distinct sequence values, the values might not be generated consecutively when all the sessions are considered. For example, with a cache setting of 10, the first session might reserve values 1 — 10 and nextval returns 1, then the second session might reserve values 11 — 20 and nextval returns 11 before nextval of the first session generates 2. Therefore, with a cache setting of 1, you can be sure that nextval values are generated sequentially. With a cache setting greater than 1, you should only assume that all the nextval values are distinct, but not generated sequentially. Also, the sequence last_value reflects the latest value reserved by any session, regardless of whether it was already returned by the nextval function.

CREATE SEQUENCE conforms to the SQL standard, with the following exceptions:

  • You need to use the nextval function instead of the standard NEXT VALUE FOR expression.

  • The OWNED BY clause is a PostgreSQL extension.

Change a sequence

You can use ALTER SEQUENCE to change the definition of a sequence generator. Any parameters that are not set in the ALTER SEQUENCE command retain their previous settings.

You must own the sequence to use ALTER SEQUENCE. To change a sequence’s schema, you must also have the CREATE privilege on the new schema. To alter the owner, you must be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the sequence schema.

ALTER SEQUENCE [ IF EXISTS ] <name>
[ AS <data_type> ]
[ INCREMENT [ BY ] <increment> ]
[ MINVALUE <minvalue> | NO MINVALUE ] [ MAXVALUE <maxvalue> | NO MAXVALUE ]
[ START [ WITH ] <start> ]
[ RESTART [ [ WITH ] <restart> ] ]
[ CACHE <cache> ] [ [ NO ] CYCLE ]
[ OWNED BY { <table_name.column_name> | NONE } ]
ALTER SEQUENCE [ IF EXISTS ] <name> OWNER TO { <new_owner> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER SEQUENCE [ IF EXISTS ] <name> RENAME TO <new_name>
ALTER SEQUENCE [ IF EXISTS ] <name> SET SCHEMA <new_schema>
Parameters of the ALTER SEQUENCE command

<name>

The name of a sequence to be altered. Can be schema-qualified (<schema_name>.<sequence_name>)

IF EXISTS

When this option is specified, PostgreSQL does not throw an error if a sequence with the specified name does not exist in the database. A notification is displayed in this case

<data_type>

The optional clause AS <data_type> changes the data type of the sequence. Valid types are smallint, integer, and bigint. Changing the data type changes the minimum and maximum values of the sequence if the previous minimum and maximum values were set by the old data type (if the sequence was created using NO MINVALUE or NO MAXVALUE, implicitly or explicitly). Otherwise, the minimum and maximum values are preserved, unless new minimum and maximum values are given as part of the same command. If the minimum and maximum values do not fit into the new data type, an error is generated

<increment>

The optional clause INCREMENT [BY] <increment> changes the value that is added to the current sequence value to create a new value. A positive value sets an ascending sequence, a negative one specifies a descending sequence. If not specified, the old increment value is retained

MINVALUE <minvalue> | NO MINVALUE

The optional clause MINVALUE <minvalue> changes the minimum value that a sequence can generate. If NO MINVALUE is specified, 1 and the minimum value of the data type for ascending and descending sequences, respectively, will be used. If neither option is specified, the current minimum value is retained

MAXVALUE <maxvalue> | NO MAXVALUE

The optional clause MAXVALUE <maxvalue> changes the maximum value for the sequence. If NO MAXVALUE is specified, the maximum value of the data type and -1 for ascending and descending sequences, respectively, will be used. If neither option is specified, the current maximum value is retained

<start>

The optional clause START WITH <start> changes the value at which to start the sequence. This has no effect on the current sequence value. It sets the value that the ALTER SEQUENCE RESTART command will use

<restart>

The optional clause RESTART [ WITH <restart> ] changes the current value of the sequence. This is similar to calling the setval function with is_called = false. The specified value will be returned by the next call of nextval. Writing RESTART with no restart value is equivalent to supplying the start value that was specified by CREATE SEQUENCE or last set by ALTER SEQUENCE START WITH

<cache>

The optional clause CACHE <cache> sets how many sequence values should be retrieved and stored in memory for faster access. The minimum value is 1 — only one value can be generated at a time (no cache). If not specified, the current cache value is retained

[ NO ] CYCLE

The CYCLE option allows the sequence to wrap around when maxvalue or minvalue is reached by an ascending or descending sequence respectively. If the limit is reached, the next generated number will be the minvalue for an ascending sequence or maxvalue for a descending sequence.

If the optional NO CYCLE key word is specified, any nextval calls after the sequence reaches its maximum value will return an error. If neither CYCLE or NO CYCLE are specified, the old cycle behavior is retained

OWNED BY <table_name.column_name> | NONE

The OWNED BY option causes the sequence to be associated with a table column. If this column or its whole table is dropped, the sequence will be automatically dropped. The specified table must have the same owner and be in the same schema as the sequence. Specifying OWNED BY NONE removes any existing association

<new_owner>

The user name of the new sequence owner

<new_name>

The new name for the sequence

<new_schema>

The new schema for the sequence

The following statement makes the mysequence sequence descending from 10 to 2 using the step 2 and the cycle option:

ALTER SEQUENCE mysequence
    INCREMENT -2
    MINVALUE 2
    MAXVALUE 10
    RESTART 10
    CYCLE;

ALTER SEQUENCE does not immediately affect nextval results in backends that have cached sequence values. They will only notice the changes after all cached values are used. The current server process responds to the changes immediately.

ALTER SEQUENCE does not affect the currval status for the sequence.

ALTER SEQUENCE blocks concurrent nextval, currval, lastval, and setval calls.

For historical reasons, ALTER TABLE can be used with sequences, but only with variants that are equivalent to the forms shown above.

Delete a sequence

The DROP SEQUENCE command removes a sequence. It can only be dropped by its owner or a superuser.

DROP SEQUENCE [ IF EXISTS ] <name> [, ...] [ CASCADE | RESTRICT ]
Parameters of the DROP SEQUENCE command

IF EXISTS

When this option is specified, PostgreSQL does not throw an error if a sequence with the specified name does not exist in the database. A notification is displayed in this case

<name> [, …​]

The name of a sequence to be removed. Can be schema-qualified (<schema_name>.<sequence_name>). You can specify multiple names separated by commas to remove multiple sequences

CASCADE

Drop objects that depend on the sequence, and in turn all objects that depend on those objects. See Dependency Tracking

RESTRICT

Refuse to drop the sequence if any objects depend on it. This is the default behavior

Example:

DROP SEQUENCE mysequence;

Work with sequences

The functions described below provide simple and multiuser-safe methods for obtaining successive values from sequence objects.

nextval

The nextval function advances the sequence object to its next value and returns that value.

nextval ( regclass ) -> bigint

If multiple sessions execute nextval concurrently, each safely receives a distinct sequence value. If the sequence object was created with default parameters, successive nextval calls will return successive values beginning with 1. Other behaviors can be specified by using appropriate parameters in the CREATE SEQUENCE command.

This function requires USAGE or UPDATE privilege on the sequence.

The example below shows how to create a sequence associated with a table column and fill the column using nextval. To do this, perform the following steps:

  1. Create a table.

    CREATE TABLE book_orders (
    order_id SERIAL,
    item_id INT NOT NULL,
    book_name VARCHAR NOT NULL,
    price NUMERIC NOT NULL,
    PRIMARY KEY(order_id, item_id)
);

  2. Create a new sequence associated with the item_id column of the book_orders table.

    CREATE SEQUENCE book_order_items START 1 OWNED BY book_orders.item_id;

  3. Add rows to the table using the nextval function to fill the item_id column.

    INSERT INTO
    book_orders(order_id, item_id, book_name, price)
VALUES
    (1, nextval('book_order_items'),'Hyperion',21),
    (1, nextval('book_order_items'),'War and Peace',26),
    (2, nextval('book_order_items'),'1984',20),
    (2, nextval('book_order_items'),'The Time Machine',19);

  4. Check the table data.

    SELECT * FROM book_orders;

    The result:

     order_id | item_id |    book_name     | price
----------+---------+------------------+-------
        1 |       1 | Hyperion         |    21
        1 |       2 | War and Peace    |    26
        2 |       3 | 1984             |    20
        2 |       4 | The Time Machine |    19

If you drop the book_orders table, the book_order_items sequence will also be dropped.

setval

The setval function sets the current value of the sequence object, and optionally its is_called flag. setval can be called with two or three parameters.

setval ( regclass, bigint [, boolean ] ) -> bigint

Where:

  • regclass is a sequence name;

  • bigint is the last_value value;

  • boolean is the is_called value.

The form with two parameters sets the last_value field to the specified value (bigint) and sets its is_called field to true, meaning that the next nextval call advances the sequence before returning a value. The currval function will also return the specified value. In the form with three parameters, is_called can be set to true or false. true has the same effect as the two-parameter form. If is_called is set to false, the next nextval call returns the specified value. The currval function will also return this value. The table below lists the setval examples and their results.

Command Value returned by nextval

SELECT setval('mysequence', 14);

15

SELECT setval('mysequence', 14, true);

15

SELECT setval('mysequence', 14, false);

14

The result returned by setval is the second argument value.

The setval function requires UPDATE privilege on the sequence.

currval

The currval function returns the value most recently obtained using nextval for the specified sequence in the current session.

currval ( regclass ) -> bigint

If nextval has never been called for this sequence in the current session, and you try to execute currval, an error occurs. Since currval returns a value for the current session, it gives a predictable result, regardless of whether nextval is called in other sessions.

This function requires USAGE or SELECT privilege on the sequence.

Example:

SELECT currval('mysequence');

The result:

 currval
---------
      14
(1 row)

lastval

The function returns the value most recently obtained by nextval in the current session for any sequence.

lastval () -> bigint

This function is identical to currval, but instead of taking the sequence name as an argument, it refers to the most recent nextval call related to any sequence in the current session. If nextval has never been called in the current session, and you try to execute lastval, an error occurs.

lastval requires USAGE or SELECT privilege on the last used sequence.

Example:

SELECT * FROM lastval();

The result:

 lastval
---------
      14
(1 row)

List sequences in a database

You can retrieve information about sequences from the sequences view stored in the information_schema schema.

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

The result:

 sequence_schema |      sequence_name       | data_type | minimum_value |    maximum_value    | increment | cycle_option
-----------------+--------------------------+-----------+---------------+---------------------+-----------+--------------
 public          | mysequence               | bigint    | 2             | 10                  | -2        | YES
 public          | book_orders_order_id_seq | integer   | 1             | 2147483647          | 1         | NO
 public          | book_order_items         | bigint    | 1             | 9223372036854775807 | 1         | NO
(3 rows)

The result includes the automatically generated book_orders_order_id_seq sequence for the order_id column of the serial type.

The pg_sequences view also provides access to useful information about each sequence in the database. For example, you can get information about sequence owners that information_schema.sequences does not contain:

SELECT sequencename, sequenceowner FROM pg_sequences;

The result:

       sequencename       | sequenceowner
--------------------------+---------------
 mysequence               | postgres
 book_orders_order_id_seq | postgres
 book_order_items         | postgres

In addition, you can also fetch data from the pg_sequence and pg_class system catalogs using the following query:

SELECT pg_class.relname, pg_sequence.* FROM pg_sequence INNER JOIN pg_class ON pg_sequence.seqrelid = pg_class.oid;

The result:

relname                 |seqrelid|seqtypid|seqstart|seqincrement|      seqmax       |seqmin|seqcache| seqcycle
------------------------+--------+--------+--------+------------+-------------------+------+--------+----------
mysequence              | 157170 |     20 |      2 |         -2 |                 10|    2 |      1 | t
book_orders_order_id_seq| 160111 |     23 |      1 |          1 |         2147483647|    1 |      1 | f
book_order_items        | 160126 |     20 |      1 |          1 |9223372036854775807|    1 |      1 | f
(3 rows)

It is also possible to use the \ds psql meta-command to list sequences.

\ds

The result:

                    List of relations
 Schema |           Name           |   Type   |  Owner
--------+--------------------------+----------+----------
 public | book_order_items         | sequence | postgres
 public | book_orders_order_id_seq | sequence | postgres
 public | mysequence               | sequence | postgres
(3 rows)
Found a mistake? Seleсt text and press Ctrl+Enter to report it