CREATE SEQUENCE

The CREATE SEQUENCE statement creates a new sequence number generator.

Examples

Generate an ascending sequence starting from 1:

SQL

CREATE SEQUENCE serial;

Generate sequence from a given start number:

SQL

CREATE SEQUENCE serial START 101;

Generate odd numbers using INCREMENT BY:

SQL

CREATE SEQUENCE serial START WITH 1 INCREMENT BY 2;

A negative INCREMENT BY creates a descending sequence:

SQL

CREATE SEQUENCE countdown START WITH 99 INCREMENT BY -1 MAXVALUE 99;

By default, cycles are not allowed. Once an ascending sequence reaches its MAXVALUE, the next call to nextval returns an error:

SQL

SELECT nextval('serial');

db error: ERROR: nextval: reached maximum value of sequence "serial" (3)

CYCLE allows cycling through the same sequence repeatedly. Once the limit is reached, the next value wraps back to the start:

SQL

SELECT nextval('serial') AS nextval;

 nextval
---------
       1

Creating and Dropping Sequences

Sequences can be created and dropped similarly to other catalog items.

Overwrite an existing sequence:

SQL

CREATE OR REPLACE SEQUENCE serial;

Only create sequence if no such sequence exists yet:

SQL

CREATE SEQUENCE IF NOT EXISTS serial;

Remove sequence:

SQL

DROP SEQUENCE serial;

Remove sequence if exists:

SQL

DROP SEQUENCE IF EXISTS serial;

Using Sequences for Primary Keys

Sequences can be used as DEFAULT values in CREATE TABLE statements.

The example below uses a sequence to create an integer primary key:

SQL

CREATE SEQUENCE id_sequence START 1;
CREATE TABLE tbl (id INTEGER PRIMARY KEY DEFAULT nextval('id_sequence'), s VARCHAR);
INSERT INTO tbl (s) VALUES ('hello'), ('world');
SELECT * FROM tbl;

 id | s
----+-------
  1 | hello
  2 | world

You can also add a sequence-backed column to an existing table with ALTER TABLE ... ADD COLUMN ... DEFAULT nextval(...). Existing rows are backfilled from the sequence and subsequent inserts continue from it:

SQL

ALTER TABLE tbl ADD COLUMN id INTEGER DEFAULT nextval('id_sequence');

Selecting the Next Value

To select the next number from a sequence, use nextval:

SQL

CREATE SEQUENCE serial START 1;
SELECT nextval('serial') AS nextval;

 nextval
---------
       1

Using this sequence in an INSERT command:

SQL

INSERT INTO distributors VALUES (nextval('serial'), 'nothing');

Selecting the Current Value

You may also view the current number from the sequence. currval returns the value nextval most recently returned in the current session, so the nextval function must have already been called in this session before calling currval, otherwise a Serialization Error (sequence is not yet defined in this session) will be thrown.

SQL

CREATE SEQUENCE serial START 1;
SELECT nextval('serial') AS nextval;
SELECT currval('serial') AS currval;

 nextval
---------
       1

 currval
---------
       1

Caching Values

CACHE makes each session take that many values at a time and hand them out from memory, as PostgreSQL does:

SQL

CREATE SEQUENCE order_ids CACHE 1000;
SELECT nextval('order_ids') AS nextval;
SELECT cache_size FROM pg_sequences WHERE sequencename = 'order_ids';

 nextval
---------
       1

 cache_size
------------
       1000

Restarting a Sequence

ALTER SEQUENCE ... RESTART sets a sequence back to its start value, and RESTART WITH value to the given value: the next nextval returns that value. As in PostgreSQL, the restart belongs to the transaction, so if the transaction rolls back, the sequence continues where it was. TRUNCATE ... RESTART IDENTITY restarts the sequences of the truncated tables' SERIAL columns the same way.

SQL

CREATE SEQUENCE invoice_ids START 100;
SELECT nextval('invoice_ids') AS a, nextval('invoice_ids') AS b;
ALTER SEQUENCE invoice_ids RESTART;
SELECT nextval('invoice_ids') AS nextval;
ALTER SEQUENCE invoice_ids RESTART WITH 500;
SELECT nextval('invoice_ids') AS nextval;

 a   | b
-----+-----
 100 | 101

 nextval
---------
     100

 nextval
---------
     500

Syntax

Syntax

CREATE SEQUENCE creates a new sequence number generator.

If a schema name is given then the sequence is created in the specified schema. Otherwise it is created in the current schema. Temporary sequences exist in a special schema, so a schema name may not be given when creating a temporary sequence. The sequence name must be distinct from the name of any other sequence in the same schema.

After a sequence is created, you use the function nextval to operate on the sequence.

Parameters

Name	Description
AS data_type	The optional clause AS data_type specifies the data type of the sequence: smallint, integer, or bigint (the default). As in PostgreSQL, the type sets the default maxvalue (or minvalue for a descending sequence), and minvalue and maxvalue must fit in it; nextval returns bigint either way.
OWNED BY	The optional clause OWNED BY table.column (or schema.table.column) makes the sequence belong to that table column: dropping the table drops the sequence, and TRUNCATE ... RESTART IDENTITY restarts it. The table must be in the same schema and have the same owner as the sequence. OWNED BY NONE, the default, makes the sequence independent. ALTER SEQUENCE name OWNED BY ... changes or removes the owner of an existing sequence; a column default that calls nextval on the sequence keeps depending on it either way.
cache	The optional clause CACHE cache specifies how many sequence numbers each session takes at a time and keeps in memory for faster access. The minimum and default value is 1 (one value at a time, no cache). As in PostgreSQL, the cache belongs to the session: with a cache larger than 1, values handed out by different sessions interleave, and the values a session took but did not use are lost when it disconnects.
CYCLE or NO CYCLE	The CYCLE option allows the sequence to wrap around when the maxvalue or minvalue has been reached by an ascending or descending sequence respectively. If the limit is reached, the next number generated will be the minvalue or maxvalue, respectively. If NO CYCLE is specified, any calls to nextval after the sequence has reached its maximum value will return an error. If neither CYCLE nor NO CYCLE are specified, NO CYCLE is the default.
increment	The optional clause INCREMENT BY increment specifies which value is added to the current sequence value to create a new value. A positive value will make an ascending sequence, a negative one a descending sequence. The default value is 1.
maxvalue	The optional clause MAXVALUE maxvalue determines the maximum value for the sequence. If this clause is not supplied or NO MAXVALUE is specified, then default values will be used. The defaults are 2^63 - 1 and -1 for ascending and descending sequences, respectively.
minvalue	The optional clause MINVALUE minvalue determines the minimum value a sequence can generate. If this clause is not supplied or NO MINVALUE is specified, then defaults will be used. The defaults are 1 and -(2^63 - 1) for ascending and descending sequences, respectively.
name	The name (optionally schema-qualified) of the sequence to be created.
start	The optional clause START WITH start allows the sequence to begin anywhere. The default starting value is minvalue for ascending sequences and maxvalue for descending ones.
TEMPORARY or TEMP	If specified, the sequence object is created only for this session, and is automatically dropped on session exit. Existing permanent sequences with the same name are not visible (in this session) while the temporary sequence exists, unless they are referenced with schema-qualified names.

Tip

Sequences are based on BIGINT arithmetic, so the range cannot exceed the range of an eight-byte integer (-9223372036854775808 to 9223372036854775807).

Limitations

Like PostgreSQL, a sequence is logged ahead of the values sessions have taken, but by a step that grows with use: 32 values (or the CACHE size, if larger) for a new sequence, then about one sixteenth of the values taken so far, rounded up to a power of two, and at most 4096. When the logged values are half used, nextval writes the next position, which is flushed to disk in the background, so a commit usually finds it durable and waits for that flush only if it has not finished yet. A committed value is never handed out twice. After a crash, a sequence resumes after its last durable position, so up to one and a half steps of values, plus the values sessions had cached, can be skipped, and a value drawn by a transaction that never committed can be handed out again. setval is durable when it returns.

A sequence OWNED BY a column belongs to the column's table: dropping the table drops it, but dropping only the column does not, unlike in PostgreSQL.

While a transaction that restarted or renamed a sequence is open, nextval and setval on that sequence in other transactions fail with a serialization error (40001) instead of waiting for it as in PostgreSQL; a session still hands out the values it has cached. Once that transaction commits, transactions that started before the commit keep getting the error, and later transactions use the restarted or renamed sequence.

When a table column uses a sequence as its DEFAULT, the column keeps a dependency on that sequence. The default can be changed with ALTER TABLE ... ALTER COLUMN ... SET DEFAULT — here it is reset to NULL, so subsequent rows no longer draw from the sequence:

SQL

CREATE SEQUENCE id_sequence START 1;
CREATE TABLE tbl (    id INTEGER DEFAULT nextval('id_sequence'),    s VARCHAR);
INSERT INTO tbl(s) VALUES ('default is the next value from id_sequence');
ALTER TABLE tbl ALTER COLUMN id SET DEFAULT NULL;
INSERT INTO tbl(s) VALUES ('default is NULL');
SELECT * FROM tbl;

 id   | s
------+--------------------------------------------
    1 | default is the next value from id_sequence
 NULL | default is NULL

While a table column still depends on the sequence through a DEFAULT nextval(...) expression, attempting to drop the sequence results in an error:

SQL

DROP SEQUENCE id_sequence;

db error: ERROR: Cannot drop entry "id_sequence" because there are entries that depend on it.
table "tbl" depends on sequence "id_sequence".
Use DROP...CASCADE to drop all dependents.

As the error message suggests, you can force dropping by adding CASCADE. This removes the dependency and the column default, but keeps the table and its existing data:

SQL

DROP SEQUENCE id_sequence CASCADE;
SELECT * FROM tbl;

 id | s
----+--------------------------------------------
  1 | default is the next value from id_sequence