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 10
CREATE SEQUENCE AS data_typeclause added (commit 2ea5b06c)
- PostgreSQL 8.2
CREATE SEQUENCE ... OWNED BY ...syntax added (commit 2b2a5072)
- PostgreSQL 7.4
NO MAXVALUEandNO MINVALUEoptions 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