NEXTVAL() tends to advance an object to another value and return it.
SEQUENCE OBJECTS are just single-row tables created from
SEQUENCES in PostgreSQL.
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
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
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.
Iter VAL 1 1 2 2 3 3 4 4 5 5
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
INSERT INTO RANDOM_GENE values (NEXTVAL('SERIAL_NUM')), (NEXTVAL('SERIAL_NUM')), (NEXTVAL('SERIAL_NUM'));
Our table will now look as follows.
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
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');
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
NEXTVAL() tends to increment the
SEQUENCE GENERATOR each time called. Hence, we don’t have to worry about duplicates in this case.
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.
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 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
Hence for the name
MATHEW, the value was already at 4, which then advanced to 5 for its
So now we hope that you have fully understood how
NEXTVAL() works and can implement it as you like.