Contents
CREATE SEQUENCE
CREATE SEQUENCE
is a DDL command to define a new sequence.
CREATE SEQUENCE
was added in PostgreSQL 6.4.
Usage
Generally sequences are created automatically when defining a table column with the SERIAL
or BIGSERIAL
pseudo-datatype. It is also of course possible to create a sequence manually using CREATE SEQUENCE
and assign it to a table column.
Sequences can be created for the int2
(smallint
), int4
(int
) and int8
(bigint
) data types.
If a column's default value is set to read from a sequence with a larger data type (e.g. int4
column with int8
sequence), an out-of-range error will occur once the sequence value exceeds the column data type's maximum value.
Note that when executing CREATE SEQUENCE
, if no data type for the sequence is specified, int8
is assumed.
Change history
- PostgreSQL 15
- syntax
CREATE UNLOGGED SEQUENCE
added (commit 344d62fb)
- syntax
- PostgreSQL 10
CREATE SEQUENCE AS data_type
clause added (commit 2ea5b06c)
- PostgreSQL 8.2
CREATE SEQUENCE ... OWNED BY ...
syntax added (commit 2b2a5072)
- PostgreSQL 7.4
NO MAXVALUE
andNO MINVALUE
options added (commit f249daf9)
- PostgreSQL 7.2
- support for temporary sequences added (commit a0c12d5e)
- PostgreSQL 6.4
- added (commit 9729f6ca)
Examples
Basic usage example for CREATE SEQUENCE
:
postgres=# CREATE SEQUENCE foo_seq; CREATE SEQUENCE postgres=# \ds+ List of relations Schema | Name | Type | Owner | Persistence | Size | Description --------+---------+----------+----------+-------------+------------+------------- public | foo_seq | sequence | postgres | permanent | 8192 bytes | (1 row) postgres=# SELECT * FROM pg_sequences\gx -[ RECORD 1 ]-+-------------------- schemaname | public sequencename | foo_seq sequenceowner | postgres data_type | bigint start_value | 1 min_value | 1 max_value | 9223372036854775807 increment_by | 1 cycle | f cache_size | 1 last_value |
Note that by default, the sequence was created as int8
(bigint
).
References
- PostgreSQL documentation: CREATE SEQUENCE