CREATE SEQUENCE

Function

CREATE SEQUENCE adds a sequence to the current database. The owner of a sequence is the user who creates the sequence.

Precautions

• A sequence is a special table that stores arithmetic progressions. It has no actual meaning and is usually used to generate unique identifiers for rows or tables.
• If a schema name is given, the sequence is created in the specified schema; otherwise, it is created in the current schema. The sequence name must be different from the names of other sequences, tables, indexes, views in the same schema.
• After the sequence is created, functions nextval() and generate_series(1,N) insert data to the table. Make sure that the number of times for invoking nextval is greater than or equal to N+1. Otherwise, errors will be reported because the number of times for invoking function generate_series() is N+1.
• By default, the maximum value of Sequence is 2^63 – 1. If a large identifier is used, the maximum value can be 2^127 – 1.
• A user granted with the CREATE ANY SEQUENCE permission can create sequences in the public and user schemas.

Syntax

CREATE [ LARGE | TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE | NOMINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE | NOMAXVALUE] 
    [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE | NOCYCLE ] 
    [ OWNED BY { table_name.column_name | NONE } ];

Parameter Description

• TEMPORARY | TEMP

  Specifies the keyword of a temporary sequence.

  

  • Enable the PostgreSQL compatibility mode for the database before creating a temporary sequence.
  • The lifecycle of a temporary sequence is at the session level, and temporary sequence objects are isolated by sessions. Sequences are automatically dropped on session exit.
  • Temporary sequences exist in a special schema. Each session has only one temporary schema. If a temporary schema is created in advance, the schema name can be provided when the temporary sequence is created. If a temporary schema is not created in advance, the schema name cannot be provided when the temporary sequence is created; in addition, each session can access only the objects in its own temporary schema and cannot access the objects in the temporary schemas of other sessions. If the accessed temporary schema does not belong to this session, an error is reported.
  • If temporary sequences exist, the existing permanent sequences with the same name (in this session) become invisible, but they can be referenced with a schema-limited name.
• name

  Specifies the name of a sequence to be created.

  Value range: a sting containing only lowercase letters, uppercase letters, special characters #_$, and digits

• increment

  Specifies the step for a sequence. A positive number generates an ascending sequence, and a negative number generates a decreasing sequence.

  The default value is 1.

  

  In B compatibility mode, if the step is a floating point number, the value is automatically converted to an integer. In other modes, this parameter cannot be set to a floating point number.

• MINVALUE minvalue | NO MINVALUE| NOMINVALUE

  Specifies the minimum value of the sequence. If MINVALUE is not declared, or NO MINVALUE is declared, the default value of the ascending sequence is 1, and that of the descending sequence is -2⁶³-1. NOMINVALUE is equivalent to NO MINVALUE.

• MAXVALUE maxvalue | NO MAXVALUE| NOMAXVALUE

  Specifies the maximum value of the sequence. If MAXVALUE is not declared, or NO MAXVALUE is declared, the default value of the ascending sequence is 2⁶³-1, and that of the descending sequence is -1. NOMAXVALUE is equivalent to NO MAXVALUE.

• start

  Specifies the start value of the sequence. The default value for an ascending sequence is minvalue and that for a descending sequence is maxvalue.

• cache

  Specifies the number of sequences stored in the memory for quick access purposes.

  Default value 1 indicates that one sequence can be generated each time.

  

  It is not recommended that you define cache and maxvalue or minvalue at the same time. The continuity of sequences cannot be ensured after cache is defined because unacknowledged sequences may be generated, causing waste of sequences.

• CYCLE

  Recycles sequences after the number of sequences reaches maxvalue or minvalue.

  If NO CYCLE is specified, any invocation of nextval would return an error after the number of sequences reaches maxvalue or minvalue.

  NOCYCLE is equivalent to NO CYCLE.

  The default value is NO CYCLE.

  If CYCLE is specified, the sequence uniqueness cannot be ensured.

• OWNED BY

  Associates a sequence with a specified column included in a table. In this way, the sequence will be deleted when you delete its associated column or the table where the column belongs to. The associated table and sequence must be owned by the same user and in the same schema. OWNED BY only establishes the association between a table column and the sequence. Sequences on the column do not increase automatically when data is inserted.

  The default value OWNED BY NONE indicates that such association does not exist.

  

  You are not advised to use the sequence created using OWNED BY in other tables. If multiple tables need to share a sequence, the sequence must not belong to a specific table.

Examples

gaussdb=# CREATE TEMPORARY SEQUENCE test;

Create an ascending sequence named serial, which starts from 101.

gaussdb=# CREATE SEQUENCE serial
 START 101
 CACHE 20;

Select the next number from the sequence.

gaussdb=# SELECT nextval('serial');
 nextval 
 ---------
      101

Select the next number from the sequence.

gaussdb=# SELECT nextval('serial');
 nextval 
 ---------
      102

Create a sequence associated with the table.

gaussdb=# CREATE TABLE customer_address
(
    ca_address_sk             integer               not null,
    ca_address_id             char(16)              not null,
    ca_street_number          char(10)                      ,
    ca_street_name            varchar(60)                   ,
    ca_street_type            char(15)                      ,
    ca_suite_number           char(10)                      ,
    ca_city                   varchar(60)                   ,
    ca_county                 varchar(30)                   ,
    ca_state                  char(2)                       ,
    ca_zip                    char(10)                      ,
    ca_country                varchar(20)                   ,
    ca_gmt_offset             decimal(5,2)                  ,
    ca_location_type          char(20)                     
);

gaussdb=# CREATE SEQUENCE serial1
 START 101
 CACHE 20
OWNED BY customer_address.ca_address_sk;
-- Delete a table and sequences.
gaussdb=# DROP TABLE customer_address;
gaussdb=# DROP SEQUENCE serial cascade;
gaussdb=# DROP SEQUENCE serial1 cascade;

Helpful Links

DROP SEQUENCE and ALTER SEQUENCE