setval()

A function for setting a sequence's current value

setval() is a system function for setting the current value of a sequence.

setval() was added in PostgreSQL 6.4.

Usage

setval ( regclass, bigint [, boolean ] ) → bigint

By default setval() sets the sequence's last_value field to the specified value and sets its is_called field to true, which means the next call to nextval() will advance the sequence before returning the next value.

If the third parameter is provided and set to false, the next call to nextval() will not advance the sequence.

Change history

Examples

Basic usage example for setval():

postgres=# CREATE SEQUENCE foo_seq;
CREATE SEQUENCE

postgres=# SELECT nextval('foo_seq');
 nextval 
---------
       1
(1 row)

postgres=# SELECT setval('foo_seq', 99);
 setval 
--------
     99
(1 row)

postgres=# SELECT nextval('foo_seq');
 nextval 
---------
     100
(1 row)

Calling setval()without advancing the sequence:

postgres=# SELECT setval('foo_seq', 999, false);
 setval 
--------
    999
(1 row)

postgres=# SELECT nextval('foo_seq');
 nextval 
---------
     999
(1 row)

It is not possible to set a sequence to a value outside its minimum or maximum values:

postgres=# CREATE SEQUENCE bar_seq MINVALUE 99;
CREATE SEQUENCE

postgres=# SELECT setval('bar_seq', 1);
ERROR:  setval: value 1 is out of bounds for sequence "bar_seq" (99..9223372036854775807)

Categories

Sequences, System function

See also

lastval(), currval(), nextval()