Help Center/ GaussDB/ Developer Guide(Centralized_V2.0-8.x)/ Database Quick Start/ Operating a Database/ Other Operations/ Managing Sequences
Updated on 2025-05-29 GMT+08:00
View PDF
Share

Managing Sequences

Context

A sequence is a database object that generates unique integers.

If NO CYCLE is specified, sequence numbers are integers that automatically increase according to a certain rule. Sequences are unique because they increase automatically. This is why they are often used as primary keys.

You can create a sequence for a column in either of the following methods:
  • Declare the column type as Table 3. The database automatically creates a sequence for the column.
  • Use the statement in CREATE SEQUENCE to create a sequence and reference nextval('sequence_name') in the default value of a table column to automatically generate a unique identifier.

Procedure

Declare the column type as sequence integer to define the identifier column. For example: 
1
2
3
4
5
 
gaussdb=# CREATE TABLE T1
(
    id    serial,
    name  text
);

If the following information is displayed, the creation is successful:

1
 
CREATE TABLE

Create a sequence and use the nextval('sequence_name') function to specify the default value of a column.

  1. Create a sequence. For details, see CREATE SEQUENCE. 
    1
    		 
    gaussdb=# CREATE SEQUENCE seq1 cache 100;

    If the following information is displayed, the creation is successful:

    1
    		 
    CREATE SEQUENCE
  2. Set the created sequence as the default value of a column. Then, the column has a unique identifier. 
    1
2
3
4
5
    		 
    gaussdb=# CREATE TABLE T2 
( 
    id   int not null default nextval('seq1'),
    name text
);

    If the following information is displayed, the default value has been specified:

    1
    		 
    CREATE TABLE
  3. Associate a sequence with a column.

    Associate a sequence with a specified column included in a table. In this way, the sequence will be dropped when you drop its joined column or the table where the column belongs to.

    1
    		 
    gaussdb=# ALTER SEQUENCE seq1 OWNED BY T2.id;

    If the following information is displayed, the operation is successful:

    1
    		 
    ALTER SEQUENCE

The preceding methods are similar, except that the second method specifies cache for the sequence. However, once cache is defined, the sequence values may be discontinuous (for example, 1, 4, and 5 are generated) and the sequence cannot be preserved. If a dependent column is specified for a sequence, the sequence will be deleted when the dependent column is deleted. A sequence shared by multiple columns is not forbidden in a database, but you are advised not to do that.

In the current version, you can specify the auto-increment column or set the default value of a column to nextval('seqname') when defining a table. You cannot add an auto-increment column or a column whose default value is nextval('seqname') to an existing table.

Parent topic: Other Operations