openGauss SQL Syntax - Sequence

define sequence

⚫ The sequence can generate a set of equally spaced values, which can be incremented automatically, mainly used for the primary key of the table. The SQL statements involved are shown in the table below.

Create a sequence

⚫ Function description

 Adds a new sequence generator to the current database. The current user is the owner of this generator.

⚫ Syntax

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

⚫ Precautions

 Sequence is a special table that stores arithmetic progressions. This table has no real meaning and is usually used to generate unique identifiers for rows or tables.

 If a schema name is given, the sequence is created in the given schema, otherwise it is created in the current schema. The sequence name must be distinct from the names of other sequences, tables, indexes, views, or foreign tables in the same schema.

 After the sequence is created, use the nextval() function and the generate_series(1,N) function of the sequence to insert data into the table. Please ensure that the number of calls to nextval is greater than or equal to N+1 times, otherwise the Calling N+1 times results in an error.

⚫ Example:

 Create a sequence seq_auto_extend, the starting point of the sequence is 10, the step size is 2, and the maximum value is 200. When the sequence reaches the maximum value, it can be looped.

postgres=# CREATE SEQUENCE seq_auto_extend START WITH 10 MAXVALUE 200 INCREMENT BY 2 CYCLE;

 Gets the next value in the sequence.

postgres=# SELECT nextval('seq_auto_extend');

 Realize the self-increment of id through sequence.

postgres=# CREATE SEQUENCE serial1 START 101 CACHE 20 ;

CREATE TABLE test (id number(6) default nextval('serial1'), name varchar(20),constraint ts_id primary key(id));

Modify sequence properties

⚫ Syntax

 Modify sequence attribution column

ALTER SEQUENCE [ IF EXISTS ] name [MAXVALUE maxvalue | NO MAXVALUE | NOMAXVALUE] [ OWNED BY { table_name.column_name | NONE } ] ;

 Modify the owner of the sequence

ALTER SEQUENCE [ IF EXISTS ] name OWNED TO new_owner;

⚫ Example

 Modify the sequence seq_auto_extend.

--Modify the step size to 4 and the maximum value to 400.

postgres=# ALTER SEQUENCE seq_auto_extend MAXVALUE 400 INCREMENT BY 4 CYCLE;

 Modify the attribution column of the sequence seq_auto_extend to b_numbe

postgres=# ALTER SEQUENCE seq_auto_extend OWNED BY bank_card.b_number;

delete sequence

⚫ Syntax

DROP SEQUENCE [ IF EXISTS ] {[schema.]sequence_name} [ , ... ] [ CASCADE | RESTRICT ];

⚫ Parameter description

 Delete also succeeds when the IF EXISTS sequence does not exist.

 l [schema_name.] sequence_name Name of the sequence to be deleted.

 CASCADE cascade removes sequence-dependent objects.  RESTRICT refuses to delete a sequence if there are any dependent objects. This is the default value.

⚫ Example

 Remove the sequence seq_auto_extend.

postgres=# DROP SEQUENCE IF EXISTS seq_auto_extend;