in the following example;
select setval('seq_test',111);
---this setval('seq_test',111) : update the current schema's seq_test
last_value =111
select setval('schemalei.seq_test',222);
---this setval('seq_test',111) : update the schemalei schema's seq_test
last_value =222
but in the setval
document(https://www.postgresql.org/docs/current/functions-sequence.html),
there is no description about it.
[pg164@localhost bin]$ ./psql -d postgres
psql (16.4)
Type "help" for help.
postgres=# \d
Did not find any relations.
postgres=#
postgres=#
postgres=# create schema schemalei;
CREATE SCHEMA
postgres=# create sequence public.seq_test ;
CREATE SEQUENCE
postgres=# create sequence schemalei.seq_test ;
CREATE SEQUENCE
postgres=# show search_path;
search_path
-----------------
"$user", public
(1 row)
postgres=# \dus
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
pg164 | Superuser, Create role, Create DB, Replication, Bypass RLS
postgres=# select * from pg_sequences;
schemaname | sequencename | sequenceowner | data_type | start_value |
min_value | max_value | increment_by | cycle | cache_size |
last_value
------------+--------------+---------------+-----------+-------------+-----------+---------------------+--------------+-------+------------+------------
public | seq_test | pg164 | bigint | 1 |
1 | 9223372036854775807 | 1 | f | 1 |
schemalei | seq_test | pg164 | bigint | 1 |
1 | 9223372036854775807 | 1 | f | 1 |
(2 rows)
postgres=# select setval('seq_test',111);
setval
--------
111
(1 row)
postgres=# select setval('schemalei.seq_test',222);
setval
--------
222
(1 row)
postgres=# select * from pg_sequences;
schemaname | sequencename | sequenceowner | data_type | start_value |
min_value | max_value | increment_by | cycle | cache_size |
last_value
------------+--------------+---------------+-----------+-------------+-----------+---------------------+--------------+-------+------------+------------
public | seq_test | pg164 | bigint | 1 |
1 | 9223372036854775807 | 1 | f | 1 | 111
schemalei | seq_test | pg164 | bigint | 1 |
1 | 9223372036854775807 | 1 | f | 1 | 222
(2 rows)
postgres=# select version();
version
-----------------------------------------------------------------------------
PostgreSQL 16.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.0, 64-bit
(1 row)
postgres=#
