The CREATE SEQUENCE statement creates a new sequence in a database. Use a sequence to auto-increment integers in a table.

Note:

This statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.

Considerations

  • Using a sequence is slower than auto-generating unique IDs with the gen_random_uuid(), uuid_v4() or unique_rowid() built-in functions. Incrementing a sequence requires a write to persistent storage, whereas auto-generating a unique ID does not. Therefore, use auto-generated unique IDs unless an incremental sequence is preferred or required.
  • A column that uses a sequence can have a gap in the sequence values if a transaction advances the sequence and is then rolled back. Sequence updates are committed immediately and aren't rolled back along with their containing transaction. This is done to avoid blocking concurrent transactions that use the same sequence.
  • For performance reasons, we discourage indexing on sequential keys. If, however, you are working with a table that must be indexed on sequential keys, you should use hash-sharded indexes. Hash-sharded indexes distribute sequential traffic uniformly across ranges, eliminating single-range hotspots and improving write performance on sequentially-keyed indexes at a small cost to read performance.
  • If a table references a sequence, and the reference explicitly specifies a database name, that database cannot be renamed. In this case, you can drop the column in the table that references the sequence, or you can modify the reference so that it does not specify the database name.

Required privileges

The user must have the CREATE privilege on the parent database.

Synopsis

CREATE opt_temp SEQUENCE IF NOT EXISTS sequence_name NO CYCLE MINVALUE MAXVALUE OWNED BY NONE column_name INCREMENT BY MINVALUE MAXVALUE START WITH integer VIRTUAL

Parameters

Parameter Description
seq_name The name of the sequence to be created, which must be unique within its database and follow the identifier rules. When the parent database is not set as the default, the name must be formatted as database.seq_name.
INCREMENT The value by which the sequence is incremented. A negative number creates a descending sequence. A positive number creates an ascending sequence.

Default: 1
MINVALUE The minimum value of the sequence. Default values apply if not specified or if you enter NO MINVALUE.

Default for ascending: 1

Default for descending: MININT
MAXVALUE The maximum value of the sequence. Default values apply if not specified or if you enter NO MAXVALUE.

Default for ascending: MAXINT

Default for descending: -1
START The first value of the sequence.

Default for ascending: 1

Default for descending: -1
NO CYCLE Currently, all sequences are set to NO CYCLE and the sequence will not wrap.
OWNED BY column_name Associates the sequence to a particular column. If that column or its parent table is dropped, the sequence will also be dropped.
Specifying an owner column with OWNED BY replaces any existing owner column on the sequence. To remove existing column ownership on the sequence and make the column free-standing, specify OWNED BY NONE.

Default: NONE
opt_temp New in v20.1: Defines the sequence as a session-scoped temporary sequence. For more information, see Temporary sequences.

Support for temporary sequences is experimental.

Sequence functions

We support the following SQL sequence functions:

  • nextval('seq_name')
  • currval('seq_name')
  • lastval()
  • setval('seq_name', value, is_called)

Temporary sequences

New in v20.1: CockroachDB supports session-scoped temporary sequences. Unlike persistent sequences, temporary sequences can only be accessed from the session in which they were created, and they are dropped at the end of the session. You can create temporary sequences on both persistent tables and temporary tables.

Warning:

This is an experimental feature. The interface and output are subject to change. For details, see the tracking issue cockroachdb/cockroach#46260.

Note:

Temporary tables must be enabled in order to use temporary sequences. By default, temporary tables are disabled in CockroachDB. To enable temporary tables, set the experimental_enable_temp_tables session variable to on.

Details

  • Temporary sequences are automatically dropped at the end of the session.
  • A temporary sequence can only be accessed from the session in which it was created.
  • Temporary sequences persist across transactions in the same session.
  • Temporary sequences cannot be converted to persistent sequences.
Note:

Like temporary tables, temporary sequences are not in the public schema. Instead, when you create the first temporary table, view, or sequence for a session, CockroachDB generates a single temporary schema (pg_temp_<id>) for all of the temporary objects in the current session for a database.

Usage

To create a temporary sequence, add TEMP/TEMPORARY to a CREATE SEQUENCE statement.

For example:

copy
icon/buttons/copy
> SET experimental_enable_temp_tables=on;
copy
icon/buttons/copy
> CREATE TEMP SEQUENCE temp_seq START 1 INCREMENT 1;
copy
icon/buttons/copy
> SHOW CREATE temp_seq;
  table_name |                                     create_statement
-------------+--------------------------------------------------------------------------------------------
  temp_seq   | CREATE TEMP SEQUENCE temp_seq MINVALUE 1 MAXVALUE 9223372036854775807 INCREMENT 1 START 1
(1 row)

Examples

Create a sequence with default settings

In this example, we create a sequence with default settings.

copy
icon/buttons/copy
> CREATE SEQUENCE customer_seq;
copy
icon/buttons/copy
> SHOW CREATE customer_seq;
   table_name  |                                     create_statement
---------------+-------------------------------------------------------------------------------------------
  customer_seq | CREATE SEQUENCE customer_seq MINVALUE 1 MAXVALUE 9223372036854775807 INCREMENT 1 START 1
(1 row)

Create a sequence with user-defined settings

In this example, we create a sequence that starts at -1 and descends in increments of 2.

copy
icon/buttons/copy
> CREATE SEQUENCE desc_customer_list START -1 INCREMENT -2;
copy
icon/buttons/copy
> SHOW CREATE desc_customer_list;
      table_name     |                                          create_statement
---------------------+-----------------------------------------------------------------------------------------------------
  desc_customer_list | CREATE SEQUENCE desc_customer_list MINVALUE -9223372036854775808 MAXVALUE -1 INCREMENT -2 START -1
(1 row)

View the current value of a sequence

To view the current value without incrementing the sequence, use:

copy
icon/buttons/copy
> SELECT * FROM customer_seq;
  last_value | log_cnt | is_called
-------------+---------+------------
           3 |       0 |   true
(1 row)
Note:
The log_cnt and is_called columns are returned only for PostgreSQL compatibility; they are not stored in the database.

If a value has been obtained from the sequence in the current session, you can also use the currval('seq_name') function to get that most recently obtained value:

> SELECT currval('customer_seq');
  currval
-----------
        3
(1 row)

List all sequences

copy
icon/buttons/copy
> SELECT * FROM information_schema.sequences;
  sequence_catalog |        sequence_schema        |   sequence_name    | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value |    minimum_value     |    maximum_value    | increment | cycle_option
-------------------+-------------------------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+---------------
  movr             | pg_temp_1585153897131110000_1 | temp_seq           | bigint    |                64 |                       2 |             0 | 1           | 1                    | 9223372036854775807 | 1         | NO
  movr             | public                        | customer_seq       | bigint    |                64 |                       2 |             0 | 1           | 1                    | 9223372036854775807 | 1         | NO
  movr             | public                        | desc_customer_list | bigint    |                64 |                       2 |             0 | -1          | -9223372036854775808 | -1                  | -2        | NO
(3 rows)

See also



YesYes NoNo