Updated on 2025-05-29 GMT+08:00

CREATE SEQUENCE

Description

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, or foreign tables 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 calling nextval is greater than or equal to N+1. Otherwise, errors will be reported because the number of times for calling function generate_series() is N+1.
  • A user granted the CREATE ANY SEQUENCE permission can create sequences in the public and user schemas.

Syntax

1
2
3
4
CREATE SEQUENCE [ IF NOT EXISTS ] 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 } ];

Parameters

  • IF NOT EXISTS

    When IF NOT EXISTS is specified, the system checks whether a relationship with the same name already exists in the current schema before creating a sequence. It is not created and a NOTICE is returned if a relationship with the same name already exists. When IF NOT EXISTS is not specified and a relationship with the same name exists in the schema, an ERROR is returned.

  • name

    Specifies the name of a sequence to be created.

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

  • increment

    Optional. 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 MySQL-compatible 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

    Optional. 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 -263-1. NOMINVALUE is equivalent to NO MINVALUE.

  • MAXVALUE maxvalue | NO MAXVALUE| NOMAXVALUE

    Optional. 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 263-1, and that of the descending sequence is -1. NOMAXVALUE is equivalent to NO MAXVALUE.

  • start

    Optional. 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

    Optional. 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. If there are requirements on the concurrency performance, see the GUC parameter session_sequence_cache.
    • cache specifies the value that a single CN/DN applies for from the GTM at a time. session_sequence_cache specifies the value of the cache that a single session applies for from the CN/DN at a time. The value is automatically discarded after the session ends.
  • CYCLE

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

    If NO CYCLE is specified, any call 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

    Optional. 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 advised not 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

  • Create an ascending sequence named seq1. The sequence starts from 101 and the step is 10.
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    gaussdb=# CREATE SEQUENCE seq1
        START 101
        INCREMENT 10;
    
    -- Select the next number from the sequence.
    gaussdb=# SELECT nextval('seq1');
     nextval 
    ---------
         101
    (1 row)
    gaussdb=# SELECT nextval('seq1');
     nextval 
    ---------
         111
    
    -- Delete the sequence.
    gaussdb=# DROP SEQUENCE seq1;
    
  • Implement the auto-increment column.
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    -- Create a table.
    gaussdb=# CREATE TABLE test1(id int PRIMARY KEY, name varchar(20));
    
    -- Create a sequence joined with the table.
    gaussdb=# CREATE SEQUENCE test_seq2
        START 1
        NO CYCLE
        OWNED BY test1.id;
    
    -- Set the default value of a column.
    gaussdb=# ALTER TABLE test1 ALTER COLUMN id SET DEFAULT nextval('test_seq2'::regclass);
    
    -- Insert data.
    gaussdb=# INSERT INTO test1 (name) values ('Joe'),('Scott'),('Ben');
    
    -- Query.
    gaussdb=# SELECT * FROM test1;
     id | name  
    ----+-------
      3 | Ben
      1 | Joe
      2 | Scott
    (3 rows)
    
    -- Delete the sequence and the table.
    gaussdb=# DROP SEQUENCE test_seq2 CASCADE;
    gaussdb=# DROP TABLE test1;
    
  • Create a cycle sequence.
    -- Create ascending sequence seq_test1. The maximum value is 3 and the sequence can be cycled.
    gaussdb=# CREATE SEQUENCE seq_test1
        START 1 
        MAXVALUE 3 
        CYCLE;
    
    -- Create a table and insert data into the table.
    gaussdb=# CREATE TABLE tbl_test1(
        c1 int DEFAULT nextval('seq_test1'),
        c2 varchar
    );
    gaussdb=# INSERT INTO tbl_test1 (c2) values ('test');
    gaussdb=# INSERT INTO tbl_test1 (c2) values ('test');
    gaussdb=# INSERT INTO tbl_test1 (c2) values ('test');
    gaussdb=# INSERT INTO tbl_test1 (c2) values ('test');
    gaussdb=# INSERT INTO tbl_test1 (c2) values ('test');
    
    -- Query table data. When the value of ascending sequence seq_test reaches the maximum value, the value starts from the minimum value again.
    gaussdb=# SELECT ctid,* FROM tbl_test1 ORDER BY 1,2;
     ctid  | c1 |  c2  
    -------+----+------
     (0,1) |  1 | test
     (0,1) |  3 | test
     (0,2) |  2 | test
     (0,3) |  1 | test
     (0,4) |  2 | test
    (5 rows)
    
    -- Delete the table and sequence.
    gaussdb=# DROP TABLE tbl_test1;
    gaussdb=# DROP SEQUENCE seq_test1;

Helpful Links

DROP SEQUENCE and ALTER SEQUENCE