In PostgreSQL, we can use the setval() function to set a sequence’s value.

We specify the value when we call the function. We also have the option of setting its is_called flag.

Syntax

The syntax goes like this:

setval ( regclass, bigint [, boolean ] ) 

Where regclass is the OID of the sequence from the pg_class system catalog view. However, Postgres allows us to pass the sequence name if we prefer. When we do this, Postgres will look up the OID behind the scenes. This eliminates the need for us to run joins across multiple tables to find its OID. 

The bigint argument is the value that we want to set the sequence to.

The optional boolean argument is a true/false flag that allows us to set the sequence’s is_called flag. We can set this to either true or false.

Example

Suppose we create a sequence like this:

CREATE SEQUENCE Sequence1;

This sequence uses the default settings (because we didn’t specify any).

Let’s use nextval() to advance the sequence multiple times:

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

Result:

 nextval | nextval | nextval | nextval 
---------+---------+---------+---------
       1 |       2 |       3 |       4

We can see that it started at 1, then incremented by 1 each time it was called (which is what we would expect since we used the default settings).

If we continue this, it will return 5, 6, 7, and so on.

However, we can use setval() to change the value:

SELECT setval('Sequence1', 2);

Result:

2

We can see that it effectively “reset” itself back to 2.

Now let’s call nextval() again:

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

Result:

 nextval | nextval | nextval | nextval 
---------+---------+---------+---------
       3 |       4 |       5 |       6

So it continued on from the value that we set with setval().

Note that the first value returned is 3, as if we had called the sequence when we set it with setval(). We can change this with an optional third argument (below).

The is_called Flag

We can optionally set the is_called flag. This is a true/false flag that determines whether or not to treat the sequence as having been advanced or not, as if we’d actually called the sequence when we used setval().

By default, the is_called flag is set to true. We can see this in the previous example, because it returned 3 the first time we used nextval() after using the setval() to set it to 2.

Let’s change the previous example so that we set the is_called flag to false:

SELECT setval('Sequence1', 2, false);

Result:

2

At first, it doesn’t appear as though anything is different. However, the difference becomes apparent when we use nextval() again:

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

Result:

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

This time the values start at 2 instead of 3 like in the previous example. That’s because we set the is_called flag to false.

Use the OID

We can alternatively use the sequence’s OID instead of its name:

SELECT setval(50398, 2);

Result:

2

This set the value of the same sequence from above. I know this because I queried the pg_class system catalog view to get the sequence’s OID.

We can get the OID like this:

SELECT oid 
FROM pg_class 
WHERE relname = 'sequence1';

Result:

50398

Even when we use the sequence name, the argument passed to setval() is actually its OID. 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.