PostgreSQL setval() Function

The PostgreSQL setval() function resets the current value of the specified sequence and returns the specified value.

`setval()` Syntax

Here is the syntax of the PostgreSQL setval() function:

setval(sequence_name TEXT, current_value BIGINT) -> BIGINT

setval(sequence_name TEXT, current_value BIGINT, is_called BOOLEAN) -> BIGINT

sequence_name: Required. The name of the sequence.
current_value: Required. The current value specified for the sequence.
is_called: Optional. Whether to recall the specified current value. If true, the current value of the setting has been used; if false, the current value of the setting has not been used. The default value is true.

The PostgreSQL setval() function returns the value of the second parameter, that is the parameter current_value.

First, let’s create a simple sequence generator named my_sequence using the CREATE SEQUENCE statement:

DROP SEQUENCE IF EXISTS my_sequence;
CREATE SEQUENCE my_sequence START 100;

Here, we create a sequence generator named my_sequence with a starting value of 100.

Then, let’s use the PostgreSQL nextval() function to advance my_sequence to its next value and return the latest value:

SELECT nextval('my_sequence');

 nextval
---------
     100

Then, let’s set the current value of my_sequence to 200 using the following statement,

SELECT setval('my_sequence', 200);

 setval
--------
    200

Let’s use the PostgreSQL nextval() function to get the next value of my_sequence:

SELECT nextval('my_sequence');

 nextval
---------
     201

Here, since we used the setval() function set the current value of the sequence to 200, so the nextval() function returns 201.

Of course, we could also make the sequence my_sequence start at 200 instead of 201, using the following statement:

SELECT setval('my_sequence', 200, false);

 setval
--------
    200

Let’s use the PostgreSQL nextval() function to get the next value of my_sequence:

SELECT nextval('my_sequence');

 nextval
---------
     200

Now, the start value of my_sequence is 200, not 201.