Sequences

Overview

A sequence is a database object used to generate a numeric queue. The values in a sequence may increase or decrease based on a specified increment. By default, a sequence has a maximum value of 2^63 – 1. If a large identifier is used, the maximum value can be extended to 2^127 – 1.

Examples

• Creating and using a sequence

  -- Create a sequence.
  gaussdb=#CREATE SEQUENCE seq_test
      START WITH 1
      INCREMENT BY 1
      MAXVALUE 99999;
  
  -- View the next value of the sequence.
  gaussdb=#SELECT nextval('seq_test');
   nextval 
  ---------
         1
  (1 row)
  
  -- Drop the sequence.
  gaussdb=#DROP SEQUENCE seq_test;
  DROP SEQUENCE

• Sequence gaps
  After the sequence cache is set, the performance can be improved, but gaps may also occur.

  -- Create a table.
  gaussdb=#CREATE TABLE tb_test(c1 int,c2 varchar);
  NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'c1' as the distribution column by default.
  HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
  CREATE TABLE
  
  -- Create a sequence associated with the table.
  gaussdb=#CREATE SEQUENCE seq_test
      START 1
      CACHE 10
      OWNED BY tb_test.c1;
  CREATE SEQUENCE
  
  -- Set the default value of a column.
  gaussdb=#ALTER TABLE tb_test ALTER COLUMN c1 SET DEFAULT nextval('seq_test'::regclass);
  ALTER TABLE
  
  -- Insert data.
  gaussdb=#INSERT INTO tb_test (c2) VALUES ('a');
  INSERT 0 1
  gaussdb=#INSERT INTO tb_test (c2) VALUES ('b');
  INSERT 0 1
  
  -- Log out of the database, log in again, and insert data again.
  gaussdb=#INSERT INTO tb_test (c2) VALUES ('c');
  INSERT 0 1
  
  -- Query data. A gap exists in the sequence.
  gaussdb=#SELECT * FROM tb_test ORDER BY c1;
   c1 | c2 
  ----+----
    1 | a
    2 | b
   11 | c
  (3 rows)
  
  -- Drop the table.
  gaussdb=#DROP TABLE tb_test;
  DROP TABLE