In PostgreSQL, a "sequence" is a database object that helps us create unique numbers automatically. PostgreSQL provides functions like SETVAL(), NEXTVAL(), CURRVAL(), and LASTVAL() to work with sequences efficiently. Each of these functions offers a distinct functionality and serves a unique purpose.

Quick Outline

This write-up will illustrate the use of sequence function in Postgres using the following content:

• How to Use NEXTVAL() in Postgres?
• How to Use CURRVAL() in Postgres?
• How to Use LASTVAL() in Postgres?
• How to Use SETVAL() in Postgres?

How to Use NEXTVAL() in Postgres?

The NEXTVAL() is a built-in sequence function in Postgres that proceeds the specified sequence to its next value and retrieves that value. Use the below-stated syntax to execute the NEXTVAL() function on a specific sequence:

SETVAL(seq_name TEXT);

Here, seq_name represents a sequence upon which the stated function will be implemented.

Example: Using NEXTVAL() 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":

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

SELECT NEXTVAL('cp_seq');

How to Use CURRVAL() in Postgres?

The CURRVAL() function is a sequence function in PostgreSQL that retrieves the current/latest value of the specified sequence in the recent session. However, invoking the CURRVAL() function before the NEXTVAL() function in the current session will throw an error. Use the below-stated syntax to execute the CURRVAL() function on a specific sequence:

CURRVAL(seq_name TEXT)

Here, seq_name represents a targeted sequence to which the stated function will be applied.

Example: Using CURRVAL() in Postgres

Invoke the CURRVAL() function on the selected sequence to see how it works:

SELECT CURRVAL('cp_seq');

How to Use LASTVAL() in Postgres?

The LASTVAL() function is a sequence function in PostgreSQL that retrieves the current/latest value of the specified sequence in the recent session. It is quite similar to CURRVAL(), but it doesn't need any argument value:

LASTVAL();

Example: Using LASTVAL() in PostgreSQL

Invoke the LASTVAL() function to get the most recent value of the NEXTVAL() function:

SELECT LASTVAL();

How to Use SETVAL() in Postgres?

SETVAL() is one of the sequence functions that resets the current value of the selected sequence and retrieves the specified value. Use the below-stated syntax to execute the SETVAL() function on a specific sequence:

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

Here, seq_name represents a sequence upon which the stated function will be implemented. The current_val indicates the current value of the selected sequence. The is_called parameter determines whether the specified current value is recalled.

Example: Using SETVAL() in Postgres

Invoke the SETVAL() function to set the sequence value to 180:

SELECT SETVAL('cp_seq', 180);

That’s all about the sequence functions in PostgreSQL.

To learn more about these sequence functions and their different use cases, read the following dedicated guides: SETVAL(), NEXTVAL(), CURRVAL(), and LASTVAL().

Conclusion

PostgreSQL provides various sequence functions like SETVAL(), NEXTVAL(), CURRVAL(), and LASTVAL() to work with sequences efficiently. For example, the NEXTVAL() function proceeds the specified sequence to its next value and retrieves that value, the SETVAL() function resets the current value of the selected sequence and retrieves the specified value, etc. This post has demonstrated the working of various sequence functions in PostgreSQL.