In PostgreSQL, the nextval() function is used to advance sequence objects to their next value and return that value. We pass the name of the sequence when we call the function. This assumes that the sequence object exists.

Syntax

The syntax goes like this:

nextval ( regclass )

Here, regclass is the sequence’s OID in the pg_class system catalog view. However, we can pass either the name of the sequence or its OID. If we pass the name, Postgres converts it into its OID for us.

Example

Let’s create a sequence:

CREATE SEQUENCE Sequence1;

Now let’s use nextval() to advance the sequence to its next value and return the result:

SELECT nextval('Sequence1');

Result:

1

In this case, I passed the sequence’s name, and Postgres converted it into the OID for me. When I created the sequence I used the default settings, and so when I called nextval() for the first time it returned 1.

Let’s call nextval() some more:

SELECT 
    nextval('Sequence1'),
    nextval('Sequence1'),
    nextval('Sequence1'),
    nextval('Sequence1');

Result:

 nextval | nextval | nextval | nextval 
---------+---------+---------+---------
       2 |       3 |       4 |       5

We can see that it advanced to the next value each time it was called.

Use the OID

We can alternatively use the sequence’s OID:

SELECT nextval(50355);

Result:

 nextval 
---------
       6

Technically, the argument passed to the nextval() function is actually its OID from the pg_class system catalog view. When we pass the sequence’s name, Postgres looks up its OID and uses that. The regclass data type’s input converter does it all for us, so we can pass the name without needing to run joins across multiple tables to find its OID. 

Use nextval() When Inserting Data

We can use the nextval() function when inserting data into a table. This allows us to have a column that contains sequential values across all rows.

Let’s create a table, then use an INSERT statement with the nextval() function:

CREATE TABLE idiots (
    IdiotId integer NOT NULL PRIMARY KEY,
    IdiotName VARCHAR
);

INSERT INTO Idiots ( IdiotId, IdiotName ) 
VALUES 
    ( nextval('Sequence1'), 'Dumb' ),
    ( nextval('Sequence1'), 'Dumber' ),
    ( nextval('Sequence1'), 'Dumbest' )
RETURNING *;

Result:

 idiotid | idiotname 
---------+-----------
       6 | Dumb
       7 | Dumber
       8 | Dumbest

Here, we inserted three rows into the table, and used the nextval() function to populate the IdiotId column.

Apply nextval() to the Column Definition

We can take the previous example a step further and apply the nextval() function to the actual column definition when we create the table. This will eliminate the need to use nextval() every time we insert a new row.

Example:

DROP TABLE IF EXISTS Idiots;

CREATE SEQUENCE idiots_idiotid_seq AS integer;
CREATE TABLE idiots (
    IdiotId integer NOT NULL DEFAULT nextval('idiots_idiotid_seq'),
    IdiotName VARCHAR
);
ALTER SEQUENCE idiots_idiotid_seq OWNED BY Idiots.IdiotId;

Here, we created a new sequence, applied it to the table definition via nextval(), and then altered the sequence so that it’s owned by the column.

Now we can insert new rows without having to call nextval() within our INSERT statement:

INSERT INTO Idiots ( IdiotName ) 
VALUES 
    ( 'Dumb' ),
    ( 'Dumber' ),
    ( 'Dumbest' )
RETURNING *;

Result:

 idiotid | idiotname 
---------+-----------
       1 | Dumb
       2 | Dumber
       3 | Dumbest