Help Center/ GaussDB/ Developer Guide(Centralized_8.x)/ SQL Reference/ Functions and Operators/ SEQUENCE Functions
Updated on 2024-08-20 GMT+08:00
View PDF
Share

SEQUENCE Functions

The sequence functions provide a simple method to ensure security of multiple users for users to obtain sequence values from sequence objects.

  • nextval(regclass)

    Description: Specifies an increasing sequence and returns a new value.

    To avoid blocking of concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value is fetched, it is considered used, even if the transaction that did the nextval later aborts. This means that aborted transactions may leave unused "holes" in the sequence of assigned values. Therefore, sequences in GaussDB cannot be used to obtain sequence without gaps.

    The nextval function can be executed only on the primary node. It is not supported on standby nodes.

    Return type: numeric

    The nextval function can be called in either of the following ways: (In example 2, the sequence name cannot contain a dot.)

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
    		 
    gaussdb=# CREATE SEQUENCE seqDemo;
-- Example 1:
gaussdb=# SELECT nextval('seqDemo'); 
 nextval
---------
       1
(1 row)
-- Example 2:
gaussdb=# SELECT seqDemo.nextval; 
 nextval
---------
       2
(1 row)
gaussdb=# DROP SEQUENCE seqDemo;
  • currval(regclass)

    Description: Returns the last value of nextval in the current session. If nextval has not been called for the specified sequence in the current session, an error is reported when currval is called.

    Return type: numeric

    The currval function can be called in either of the following ways: (In example 2, the sequence name cannot contain a dot.)

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
    		 
    gaussdb=# CREATE SEQUENCE seq1;
gaussdb=# SELECT nextval('seq1'); 
-- Example 1:
gaussdb=# SELECT currval('seq1'); 
 currval
---------
       1
(1 row)
-- Example 2:
gaussdb=# SELECT seq1.currval; 
 currval
---------
       1
(1 row)
gaussdb=# DROP SEQUENCE seq1;
  • lastval()

    Description: Returns the last value of nextval in the current session. This function is equivalent to currval, except that it does not use sequence names as parameters. It fetches the sequence used by nextval last time in the current session. If nextval has not been called in the current session, an error is reported when lastval is called.

    Return type: numeric

    Example:

    1
2
3
4
5
6
7
8
    		 
    gaussdb=# CREATE SEQUENCE seq1;
gaussdb=# SELECT nextval('seq1'); 
gaussdb=# SELECT lastval(); 
 lastval
---------
       1
(1 row)
gaussdb=# DROP SEQUENCE seq1;
  • setval(regclass, numeric)

    Sets the current value of a sequence.

    Return type: numeric

    Example:

    1
2
3
4
5
6
7
8
    		 
    gaussdb=# CREATE SEQUENCE seqDemo;
gaussdb=# SELECT nextval('seqDemo'); 
gaussdb=# SELECT setval('seqDemo',5);
 setval
--------
      5
(1 row)
gaussdb=# DROP SEQUENCE seqDemo;
  • setval(regclass, numeric, Boolean)

    Sets the current value of a sequence and the is_called sign.

    Return type: numeric

    Example:

    1
2
3
4
5
6
7
8
    		 
    gaussdb=# CREATE SEQUENCE seqDemo;
gaussdb=# SELECT nextval('seqDemo'); 
gaussdb=# SELECT setval('seqDemo',5,true);
 setval
--------
      5
(1 row)
gaussdb=# DROP SEQUENCE seqDemo;

    The current session will take effect immediately after setval is performed. If other sessions have buffered sequence values, setval will take effect only after the values are used up. Therefore, to prevent sequence value conflicts, you are advised to use setval with caution.

    Because the sequence is non-transactional, changes made by setval will not be canceled when a transaction rolled back.

    The nextval function can be executed only on the primary node. It is not supported on standby nodes.

  • pg_sequence_last_value(sequence_oid oid, OUT cache_value int16, OUT last_value int16)

    Description: Obtains the parameters of a specified sequence, including the cache value and current value.

    Return type: int16, int16

  • last_insert_id()

    Description: Gets the first auto-generated value that was last successfully inserted for an auto-increment column.

    Return type: int16

    Example: 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
    		 
    gaussdb=# CREATE TABLE animals_test (
gaussdb(# id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
gaussdb(# name CHAR(30) NOT NULL
gaussdb(# );
NOTICE:  CREATE TABLE will create implicit sequence "animals_test_id_seq" for serial column "animals_test.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "animals_test_pkey" for table "animals_test"
CREATE TABLE
gaussdb=# SELECT last_insert_id();
 last_insert_id
----------------
              0
(1 row)
gaussdb=# INSERT INTO animals_test (name) VALUES ('dog');
INSERT 0 1
gaussdb=# SELECT last_insert_id();
 last_insert_id
----------------
              1
(1 row)
  • last_insert_id(int16)

    Description: Sets the return value of the next last_insert_id() function and returns the value. If the parameter is NULL, set the return value of the next last_insert_id() function to 0. This function returns NULL.

    Return type: int16

    Example:

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
    		 
    gaussdb=# SELECT last_insert_id(100);
 last_insert_id
----------------
            100
(1 row)
gaussdb=# SELECT last_insert_id();
 last_insert_id
----------------
            100
(1 row)
    • last_insert_id() and last_insert_id(int16) are session-level functions. If no data is inserted into the auto-increment column in the current session, last_insert_id() returns 0.
    • last_insert_id() and last_insert_id(int16) are available only when sql_compatibility is set to 'B'.
Parent topic: Functions and Operators