CREATE SEQUENCE

Function

CREATE SEQUENCE adds a sequence to the current database.

A sequence is a database object that generates a series of unique, ascending, or descending values. It is mainly used to generate the primary key or unique identifier in a table.

Precautions

The user who creates a sequence is the owner of the sequence.
If a schema name is specified, the sequence is created in the specified schema. Otherwise, the text search configuration 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.
A sequence cannot be created in the template1 database.

Syntax

     
          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 } ];

Parameter Description

**Table 1** CREATE SEQUENCE parameters
Parameter	Description	Value Range
IF NOT EXISTS	If IF NOT EXISTS is specified and a sequence with the same name does not exist, the sequence can be created successfully. If a sequence with the same name already exists during sequence creation, the system will display a message indicating that the sequence already exists and no further operations will be performed. No error will be reported. This parameter is supported only by 9.1.0 and later versions.	-
name	Specifies the name of the sequence to be created.	A string compliant with the identifier naming rules.
increment	Specifies the step for a sequence. If the increment is a positive value, an increasing sequence is to be created. If the increment is a negative value, a decreasing sequence is to be created. The default value is 1.	-
MINVALUE minvalue \| NO MINVALUE\| NOMINVALUE	Specifies the minimum value that can be generated by the sequence. If MINVALUE is not specified, or NO MINVALUE is specified, 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 that can be generated by 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 ascending sequences is minvalue and for descending sequences maxvalue.	-
cache	Specifies the number of sequence numbers that are cached (pre-allocated) to the memory in a database for faster access. Within a cache period, the CN does not request a sequence number from the GTM. Instead, the CN uses the sequence number that is locally applied for in advance. In cluster versions 9.1.0.100 and later, the default value is specified by the GUC parameter default_sequence_cache. In a newly installed cluster of 9.1.0.100 or later, the default value is 20. If the cluster is upgraded from an earlier version to 9.1.0.100 or later, the default value is 1. Indicates that only one value can be generated at a time, that is, no cache.	It is not recommended that you define cache, and maxvalue, and minvalue at the same time. The continuity of sequences cannot be ensured after cache is defined because unacknowledged sequences may be generated, wasting sequence number segments. You are advised not to set a large value for cache (less than 100000000). Otherwise, it takes a long time to cache the sequence number (the first NEXTVAL in each cache period). Set a proper value for cache based on services to ensure quick access without wasting sequence numbers.
CYCLE \| NOCYCLE	Recycles sequences after the number of sequences reaches maxvalue or minvalue. The default value is NO CYCLE, indicating that no new value can be generated after the sequence reaches the maximum or minimum value. NOCYCLE is equivalent to NO CYCLE. If the sequence is defined as CYCLE, the sequence uniqueness cannot be ensured. If NO CYCLE is specified, any invocation of nextval would return an error after the sequence reaches its maximum value.	-
OWNED BY	Associates a sequence with a specified column of a specific table. If the column or the table where the column is located is deleted, the sequence is automatically deleted. The associated table and sequence must be owned by the same user and in the same schema. If the default value is OWNED BY NONE, indicating that such association does not exist.	OWNED BY only establishes the association between a table column and the sequence. The sequence is not created for this column. 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

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

     
          CREATE SEQUENCE serial
 START 101
 CACHE 20;

Select the next number from the sequence:

     
          SELECT nextval('serial');
 nextval 
 ---------
      101

Select the next number from the sequence:

     
          SELECT nextval('serial');
 nextval 
 ---------
      102

Create a sequence associated with the table:

     
      
        
        
          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)                     
) ;

CREATE SEQUENCE serial1
 START 101
 CACHE 20
OWNED BY customer_address.ca_address_sk;

         

       

     
    

Use SERIAL to create a serial table serial_table for primary key auto-increment.

     
          CREATE TABLE serial_table(a int, b serial);
INSERT INTO serial_table (a) VALUES (1),(2),(3);
SELECT * FROM serial_table ORDER BY b;
 a | b
---+---
 1 | 1
 2 | 2
 3 | 3
(3 rows)