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; CREATE SEQUENCE -- View the next value of the sequence. gaussdb=# SELECT nextval('seq_test'); nextval --------- 1 (1 row) -- Check the current value of the sequence. gaussdb=# SELECT currval('seq_test'); currval --------- 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); 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; c1 | c2 ----+---- 1 | a 2 | b 11 | c (3 rows) -- Drop the table. gaussdb=# DROP TABLE tb_test;
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot