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.