: Documentation: 14: CREATE SEQUENCE : Postgres Professional

CREATE SEQUENCE
Prev	Up	6.5. SQL Commands	Home	Next

CREATE SEQUENCE

CREATE SEQUENCE — define a new sequence generator

Synopsis

CREATE 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 } ]
    WITH ( [ global ],
           [ block_size = block_size ]
         )

Description

Shardman extensions to the CREATE SEQUENCE command enable creation of global sequence number generators. This command creates an ordinary PostgreSQL sequence on all nodes in a cluster and records sequence parameters in the global sequence state dictionary. (See Section 7.6 for details.)

After a global sequence is created, usual nextval function can be used to generate next sequence values that are guaranteed be unique across the entire cluster. Other standard sequence manipulation functions (e.g. setval) must not be used on global sequences as this may lead to unexpected results.

Parameters

In addition to the parameters recognized by PostgreSQL, the following parameters are supported by Shardman.

global #: If specified, the sequence object is created as a Shardman-managed global sequence.
block_size #: The number of elements allocated for a local sequence. The default value is 65536.

Notes

Global sequences are meant to behave similarly to ordinary PostgreSQL sequences (see CREATE SEQUENCE) with some limitations, the most important one being that a global sequence is always increasing. There's no support for negative increment values or wraparound (as in CYCLE), which also means there's practically no difference between the minimum sequence value and its starting value, so both parameters cannot be provided at the same time to avoid confusion.

Just like with regular sequence objects, the DROP SEQUENCE command removes a global sequence and the ALTER SEQUENCE command allows changing some of the global sequence parameters.

Examples

Create a global sequence called serial.

CREATE SEQUENCE serial MINVALUE 100 WITH (global);

Select the next number from this sequence:

SELECT nextval('serial');
 nextval
---------
     100
(1 row)