PostgreSQL supports the concept of "sequences", which are database objects and are used for generating unique integers. Sequences are primarily used to generate primary keys for tables. PostgreSQL provides several functions, such as SETVAL(), NEXTVAL(), CURRVAL(), and LASTVAL(), to work efficiently with sequences.

This write-up will illustrate the use of the SETVAL() function in PostgreSQL sequences.

What Does SETVAL() Function Do in PostgreSQL?

SETVAL() is one of the sequence functions that resets the current value of the selected sequence and retrieves the specified value.

Syntax

Use the below-stated syntax to execute the SETVAL() function on a specific sequence:

SETVAL(seq_name TEXT, current_val BIGINT, is_called BOOLEAN);

Parameters

The SETVAL() function accepts the following parameters:

- seq_name: It represents a sequence upon which the stated function will be implemented.
- current_val: It indicates the current value of the selected sequence.
- is_called: It determines whether the specified current value is recalled. True indicates that the current value of the setting will be used, while false indicates that it won't be used. By default, the “is_called” parameter has a true value.

Return Value

It retrieves the value of the “current_val” parameter.

Return Type

The return type of the SEVAL() function is “BIGINT”.

Example: Using SETVAL() in Postgres

First, create a sample sequence and name it “cp_seq”:

CREATE SEQUENCE cp_seq 
START 172;

The specified sequence is initialized with a start value of "172":

Now execute the NEXTVAL() function to proceed the cp_sequence to its next value and retrieve the current value:

SELECT NEXTVAL('cp_seq');

Now invoke the SETVAL() function to set the sequence value to 180:

SELECT SETVAL('cp_seq', 180);

Let’s invoke the NEXTVAL() function to obtain the next value of the “cp_seq”:

SELECT NEXTVAL('cp_seq');

The NEXTVAL() retrieves the sequence value according to the specified value, i.e., “set value + 1”:

If a user wants to start the sequence from the specified value (instead of specified value + 1), then specify the value of the is_called parameter as “false”:

SELECT SETVAL('cp_seq', 180, false);

This time, invoking the NEXTVAL() function will retrieve 180 instead of 181:

SELECT NEXTVAL('cp_seq');

That’s all about the usage of the NEXTVAL() function in PostgreSQL.

Conclusion

In PostgreSQL, SETVAL() is one of the sequence functions that resets the current value of the selected sequence and retrieves the specified value. The SETVAL() function accepts three parameters: sequence name, current value, and the is_called parameter. The “is_called” parameter determines whether the specified current value is recalled. Its default value is true. This post has demonstrated the working of the SETVAL() function in PostgreSQL.