Updated on 2023-10-23 GMT+08:00

Creating and Managing Sequences

Background

A sequence is a database object that generates unique integers. Sequence numbers are generated 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:
  • Set the data type of a column to Table 3. A sequence will be automatically created by the database for this column.
  • Run the CREATE SEQUENCE statement to create a sequence. Set the initial value of the nextval('sequence_name') function to the default value of a column.

Procedure

Method 1: Set the data type of a column to a sequence integer. For example:
1
2
3
4
5
openGauss=# CREATE TABLE T1
(
    id    serial,
    name  text
);

If the following information is displayed, the table has been created:

1
CREATE TABLE

Method 2: Create a sequence and set the initial value of the nextval('sequence_name') function to the default value of a column.

  1. Create a sequence.
    1
    openGauss=# CREATE SEQUENCE seq1 cache 100;
    

    If the following information is displayed, the sequence has been created:

    1
    CREATE SEQUENCE
    
  2. Set the initial value of the nextval('sequence_name') function to the default value of a column.
    1
    2
    3
    4
    5
    openGauss=# 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 the sequence with a column.

    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.

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

    If the following information is displayed, the column has been specified:

    1
    ALTER SEQUENCE
    

The preceding methods are similar, except that the second method specifies cache for the sequence. A sequence having cache defined has inconsecutive values (such as 1, 4, and 5) and cannot maintain the order of its values. After the dependent column of a sequence has been specified, once the sequence is deleted, the sequence of the dependent will be deleted. A sequence shared by multiple columns is not forbidden in a database, but you are not advised 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.