NEXTVAL Function in PostgreSQL

NEXTVAL() tends to advance an object to another value and return it. SEQUENCE OBJECTS are just single-row tables created from SEQUENCES in PostgreSQL.

The NEXTVAL() function is defined under the SEQUENCE MANIPULATION FUNCTIONS.

nextval ( regclass ) ? bigint

REGCLASS refers to the SEQUENCE OBJECT, and this function tends to return the BIGINT. Now let’s see some possible implementations of this NEXTVAL() function and how we can use it.

Practical Example of NEXTVAL() in PostgreSQL

One of the uses of SEQUENCE IDENTIFIERS in PostgreSQL is how they can be used for getting unique row identifier values. You can read more about them under the CREATE SEQUENCE URL.

We will follow up on the usage given above and then try to implement a system that can effectively use the NEXTVAL() function. We will boot up PostgreSQL and create a simple table called RANDOM_GEN (short for random generator).

CREATE TABLE RANDOM_GENE(
	val INT PRIMARY KEY
);

We have used a simple VALUE as a PRIMARY KEY in our table as it will be unique and random. Now let’s go ahead and append some data to this table.

Of course, the VAL should be RANDOM to use the SEQUENCE GENERATOR in PostgreSQL. Our tutorial will use a SERIAL GENERATOR with incrementing values to get UNIQUE VAL.

CREATE SEQUENCE serial_num;

And to use the values from this SEQUENCE GENERATOR, we can query a SELECT operation from this table.

SELECT * from SERIAL_NUM;

But there is an issue. If we repeatedly call this SELECT operation, we tend to get the same value from the SEQUENCE GENERATOR.

So if we were to INSERT values from this SEQUENCE to our table without testing, we would have ended up with duplicate values.

And this is where the NEXTVAL() function comes in. We can go ahead and advance the value of this GENERATOR and then issue the SELECT operation to get the ascending values.

So we can write:

SELECT * from NEXTVAL('serial_num');

And this will return output as follows for, let’s say, 5 iterations.

Output:

Iter      VAL
1          1
2          2
3          3
4          4
5          5

So NEXTVAL(), as we saw, tends to increase the SEQUENCE GENERATOR and advance it. So each time NEXTVAL() is called for the SEQUENCE passed in its argument, we can imagine the SEQUENCE to point itself to the next ascending value.

Hence we can now call something as follows for an INSERT operation into our RANDOM_GEN table.

INSERT INTO RANDOM_GENE values (NEXTVAL('SERIAL_NUM')), (NEXTVAL('SERIAL_NUM')), (NEXTVAL('SERIAL_NUM'));

Our table will now look as follows.

Output:

        val
1       1
2       2
3       3

Another simple way of achieving this for just the VALUE column would be to define NEXTVAL() right in the CREATE TABLE statement. We can write a query as follows.

CREATE TABLE RANDOM_GENE(
	val INT primary key default NEXTVAL('SERIAL_NUM')
);

We have used DEFAULT to define the default behavior of this VAL column that is getting values from the SEQUENCE GENERATOR. Of course, the above is useless without a secondary column, so let’s add a USER_NAME column to define the USERS receiving the RANDOM_GEN values.

CREATE TABLE RANDOM_GENE(
	val INT primary key default NEXTVAL('SERIAL_NUM'),
        name TEXT
);

We can now write an INSERT statement as follows.

INSERT into RANDOM_GENE (name) values ('John'), ('Marta'), ('Alex');

This will INSERT the names given in the query to our table with the respective values obtained in ascending order from our SEQUENCE GENERATOR. If we tend to look at the table now, it would be as follows.

val     name
1	"John"
2	"Marta"
3	"Alex"

Hence, we have now fully understood how NEXTVAL() works. As for now, let’s look at the working of NEXTVAL() in different environments.

NEXTVAL() in Different Environments and Circumstances in PostgreSQL

Using NEXTVAL() tends to increment the SEQUENCE GENERATOR each time called. Hence, we don’t have to worry about duplicates in this case.

Once NEXTVAL() is called, the GENERATOR is advanced and computes the next value. Any other query concurrently running that calls the NEXTVAL() for the same SEQUENCE will get the generator’s entirely different and unique value.

Hence, it is efficient and safe to use NEXTVAL() for multiple transactions and processes that may be executing queries on a Postgres server.

Gaps and Value Differences From NEXTVAL() in PostgreSQL

A common issue found in NEXTVAL() is the strict implementation of advancement. Once the SEQUENCE advances to the new values, it is highly unlikely for it to return back or even check to see if a previous value has been used or not.

So in case you have a table with a UNIQUE value column and a NAME, and somehow the NAME you are trying to INSERT is already present in the table, you can get a violation error. NEXTVAL() would have already been called in that scenario.

And the SEQUENCE advanced to a value, but the violation prevents the INSERTION. So the next time INSERT is called, NEXTVAL() will advance once again, and the former value will be entirely skipped.

The same can be said for ON CONFLICT operations or those that may not execute properly. In the example given below, use the following to call an INSERT on the table column NAME.

name TEXT unique

And then, we INSERT data into our table with an intentional duplicate name for ALEX with a query as follows.

INSERT into RANDOM_GENE (name) values ('John'), ('Marta'), ('Alex'), ('Alex'), ('Mathew') on conflict (name) do nothing;

We have put ON CONFLICT to bypass the duplicate violation error and check our table for the VALUE. The table now looks as follows.

val    name
1	"John"
2	"Marta"
3	"Alex"
5	"Mathew"

You can see how there is no number 4 value for the column VAL. And that is because the duplicate ALEX insertion did advance the SEQUENCE GENERATOR but did not get INSERTED.

Hence for the name MATHEW, the value was already at 4, which then advanced to 5 for its INSERTION.

So now we hope that you have fully understood how NEXTVAL() works and can implement it as you like.