PolarDB:Obtain values from a sequence

Obtain the next value in a sequence

[<schema_name>.]<sequence_name>.NEXTVAL

You can specify seq1.nextval as a value in the INSERT statement that is used to insert data. The query operation is pushed down to the data node layer for execution.

• Method 1: use the SELECT statement

  SELECT seq1.nextval;

  Sample result:

    +--------------+
    | seq1.NEXTVAL |
    +--------------+
    |            2 |
    +--------------+

• Method 2: use the INSERT statement

  INSERT INTO some_users (name, address, gmt_create, gmt_modified, intro) VALUES ('sun', seq1.nextval, now(), now(), 'aa');

  Note

  If an auto-increment column is configured in a table when you create the table, you do not need to specify an auto-increment column in the INSERT statement or you can specify a value of 0 or null as the value of the auto-increment column configuration. PolarDB-X automatically maintains values in the sequence and inserts a sequence value in the INSERT statement.

Obtain the next batch of values in a sequence

SELECT [<schema_name>.]<sequence_name>.NEXTVAL [ FROM DUAL ] WHERE COUNT = <numeric value>

You can execute the following statement to obtain 10 values from a sequence at a time:

SELECT seq1.nextval FROM DUAL WHERE COUNT = 10;

Sample result:

+--------------+
| seq1.NEXTVAL |
+--------------+
|            3 |
|            4 |
|            5 |
|            6 |
|            7 |
|            8 |
|            9 |
|           10 |
|           11 |
|           12 |
+--------------+

Obtain the current value in a sequence

[<schema_name>.]<sequence_name>.CURRVAL

You can execute the following statement to obtain the current value from the sequence in the current session:

SELECT seq1.currval;

Sample result:

+--------------+
| seq1.CURRVAL |
+--------------+
|           12 |
+--------------+