pg_sequence
pg_sequence is a system catalogue table which stores information about sequences, in combination with the sequence's main entry in pg_class.
pg_sequence was added in PostgreSQL 10.
Notes
This change moves much of the metadata associated with a sequence from its associated relation into this system catalogue table. This means ALTER SEQUENCE updates are now fully transactional.
The system catalogue view pg_sequences displays the sequence information in a more accessible form, and column names match those formerly present in the sequence relation.
See the PostgreSQL 10 release notes for more details of the change.
Definition by PostgreSQL version
pg_sequence (PostgreSQL 14)
Table "pg_catalog.pg_sequence"
Column | Type | Collation | Nullable | Default
--------------+---------+-----------+----------+---------
seqrelid | oid | | not null |
seqtypid | oid | | not null |
seqstart | bigint | | not null |
seqincrement | bigint | | not null |
seqmax | bigint | | not null |
seqmin | bigint | | not null |
seqcache | bigint | | not null |
seqcycle | boolean | | not null |
Indexes:
"pg_sequence_seqrelid_index" PRIMARY KEY, btree (seqrelid)
Documentation: pg_sequence
pg_sequence (PostgreSQL 13)
Table "pg_catalog.pg_sequence"
Column | Type | Collation | Nullable | Default
--------------+---------+-----------+----------+---------
seqrelid | oid | | not null |
seqtypid | oid | | not null |
seqstart | bigint | | not null |
seqincrement | bigint | | not null |
seqmax | bigint | | not null |
seqmin | bigint | | not null |
seqcache | bigint | | not null |
seqcycle | boolean | | not null |
Indexes:
"pg_sequence_seqrelid_index" UNIQUE, btree (seqrelid)
Documentation: pg_sequence
pg_sequence (PostgreSQL 12)
Table "pg_catalog.pg_sequence"
Column | Type | Collation | Nullable | Default
--------------+---------+-----------+----------+---------
seqrelid | oid | | not null |
seqtypid | oid | | not null |
seqstart | bigint | | not null |
seqincrement | bigint | | not null |
seqmax | bigint | | not null |
seqmin | bigint | | not null |
seqcache | bigint | | not null |
seqcycle | boolean | | not null |
Indexes:
"pg_sequence_seqrelid_index" UNIQUE, btree (seqrelid)
Documentation: pg_sequence
pg_sequence (PostgreSQL 11)
Table "pg_catalog.pg_sequence"
Column | Type | Collation | Nullable | Default
--------------+---------+-----------+----------+---------
seqrelid | oid | | not null |
seqtypid | oid | | not null |
seqstart | bigint | | not null |
seqincrement | bigint | | not null |
seqmax | bigint | | not null |
seqmin | bigint | | not null |
seqcache | bigint | | not null |
seqcycle | boolean | | not null |
Indexes:
"pg_sequence_seqrelid_index" UNIQUE, btree (seqrelid)
Documentation: pg_sequence
pg_sequence (PostgreSQL 10)
Table "pg_catalog.pg_sequence"
Column | Type | Collation | Nullable | Default
--------------+---------+-----------+----------+---------
seqrelid | oid | | not null |
seqtypid | oid | | not null |
seqstart | bigint | | not null |
seqincrement | bigint | | not null |
seqmax | bigint | | not null |
seqmin | bigint | | not null |
seqcache | bigint | | not null |
seqcycle | boolean | | not null |
Indexes:
"pg_sequence_seqrelid_index" UNIQUE, btree (seqrelid)
Documentation: pg_sequence
Change history
This table has not been modified since it was added in PostgreSQL 10.
- PostgreSQL 10
- added (commit 1753b1b0)
Examples
In PostgreSQL 9.6 and earlier, the metadata associated with a sequence was stored in its associated relation, e.g.:
postgres=# CREATE SEQUENCE foo; CREATE SEQUENCE
postgres=# SELECT * from public.foo; -[ RECORD 1 ]-+-------------------- sequence_name | foo last_value | 1 start_value | 1 increment_by | 1 max_value | 9223372036854775807 min_value | 1 cache_value | 1 log_cnt | 0 is_cycled | f is_called | f
From PostgreSQL 10, the sequence relation only contains the fields modified by the nextval() function (last_value, log_cnt and is_called), e.g.:
postgres=# CREATE SEQUENCE foo;
CREATE SEQUENCE
postgres=# SELECT * from public.foo;
last_value | log_cnt | is_called
------------+---------+-----------
1 | 0 | f
(1 row)
and the remaining metadata is stored in pg_sequence, e.g.:
postgres=# SELECT * FROM pg_sequence WHERE seqrelid='foo'::REGCLASS; -[ RECORD 1 ]+-------------------- seqrelid | 16455 seqtypid | 20 seqstart | 1 seqincrement | 1 seqmax | 9223372036854775807 seqmin | 1 seqcache | 1 seqcycle | f
References
- PostgreSQL documentation: pg_sequence
Useful links
- Checking the sequences status on a single pass - 2019 blog entry from Luca Ferrari