CREATE SEQUENCE

An SQL command to define a new 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

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).

Categories

DDL, Sequences, SQL command

See also

ALTER SEQUENCE, DROP SEQUENCE, pg_sequence, pg_sequences