CREATE SEQUENCE

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

The “ statement performs a schema change. For more information about how online schema changes work in CockroachDB, see .

Considerations

• Using a sequence is slower than and is likely to cause performance problems due to . Incrementing a sequence requires a write to persistent storage. In CockroachDB, all writes are . In , replicated writes can add cross-region latency (for example, in a configuration) and become a throughput bottleneck for write-heavy workloads. Cached sequences can reduce the frequency of these writes, though gaps in sequence values may occur if cached values are lost. Auto-generating a unique ID does not require a replicated write. Therefore, use auto-generated unique IDs unless an incremental sequence is preferred or required. For more information, refer to .
• 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.
• We . If a table must be indexed on sequential keys, use . Hash-sharded indexes distribute sequential traffic uniformly across ranges, eliminating single-range and improving write performance on sequentially-keyed indexes at a small cost to read performance.
• By default, you cannot create sequences that are columns in tables in other databases. You can enable such sequence creation by setting the sql.cross_db_sequence_owners.enabled to true.

Required privileges

The user must have the CREATE on the parent database.

Synopsis

Parameters

Parameter	Description
seq_name	The name of the sequence to be created, which must be unique within its database and follow the . 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 [WITH]	The first value of the sequence. Default for ascending: 1 Default for descending: -1
RESTART [WITH]	Sets nextval to the specified number, or back to the original START value.
NO CYCLE	All sequences are set to NO CYCLE and the sequence will not wrap.
CACHE	The number of sequence values to cache in memory at the level. All sessions on the node share the same cache, which can be concurrently accessed, and which reduces the chance of creating large gaps between generated IDs. A cache size of 1 means that there is no cache, and cache sizes of less than 1 are not valid. Default: 1 (sequences are not cached by default). While this parameter caches sequences per node, to cache sequence values per session, use PER SESSION CACHE explicitly.
PER NODE CACHE	The number of sequence values to cache in memory at the node level. All sessions on the node share the same cache, which can be concurrently accessed, and which reduces the chance of creating large gaps between generated IDs. A cache size of 1 means that there is no cache, and cache sizes of less than 1 are not valid. This is the default caching strategy, and is equivalent to specifying CACHE.
PER SESSION CACHE	The number of sequence values to cache in memory for reuse within a single . A cache size of 1 means that there is no cache, and cache sizes of less than 1 are not valid.
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	Defines the sequence as a session-scoped temporary sequence. For more information, see Temporary sequences.

Sequence functions

CockroachDB supports the following :

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

Temporary sequences

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 .

This feature is in and subject to change. To share feedback and/or issues, contact Support.

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

Like , 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 to a CREATE SEQUENCE statement.

For example:

> SET experimental_enable_temp_tables=on;

> CREATE TEMP SEQUENCE temp_seq START 1 INCREMENT 1;

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

> CREATE SEQUENCE customer_seq;

> SHOW CREATE customer_seq;

   table_name  |                                     create_statement
---------------+-------------------------------------------------------------------------------------------
  customer_seq | CREATE SEQUENCE customer_seq MINVALUE 1 MAXVALUE 9223372036854775807 INCREMENT 1 START 1
(1 row)

Use a sequence when creating a table

In this example, we , using the for a , with the customer_seq sequence as its input:

CREATE TABLE customers (
    uid UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    rownum INT DEFAULT nextval('customer_seq'),
    name STRING
);

Inserting into this table with an INSERT statement that relies on default values will call nextval, which increments the sequence.

> INSERT INTO customers (name) VALUES ('Max'), ('Alice');

> SELECT * FROM customers;

                  uid                  | rownum | name
---------------------------------------+--------+--------
  1c7f5b79-88c4-49ec-b40b-6098d28bb822 |      2 | Alice
  7ce844af-6a3f-4c52-ba07-25623f345804 |      1 | Max
(2 rows)

View the current value of a sequence

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

> SELECT * FROM customer_seq;

  last_value | log_cnt | is_called
-------------+---------+------------
           2 |       0 |   true
(1 row)

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
-----------
        2
(1 row)

Set the next value of a sequence

In this example, we’re going to change the next value of customer_seq using the . Currently, the next value will be 3 (i.e., 2 + INCREMENT 1). We will change the next value to 5.

You cannot set a value outside the MAXVALUE or MINVALUE of the sequence.

> SELECT setval('customer_seq', 5, false);

  setval
----------
       5
(1 row)

The setval('seq_name', value, is_called) function in CockroachDB SQL mimics the setval() function in PostgreSQL, but it does not store the is_called flag. Instead, it sets the value to val - increment for false or val for true.

Let’s add another record to the table to check that the new record adheres to the new next value.

> INSERT INTO customers (name) VALUES ('Sam');

> SELECT * FROM customers;

                  uid                  | rownum | name
---------------------------------------+--------+--------
  19ffe03d-5eac-4a2f-8aa8-1569b998aa44 |      5 | Sam
  1c7f5b79-88c4-49ec-b40b-6098d28bb822 |      2 | Alice
  7ce844af-6a3f-4c52-ba07-25623f345804 |      1 | Max
(3 rows)

Create a sequence with user-defined settings

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

> CREATE SEQUENCE desc_customer_list START -1 INCREMENT -2;

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

List all sequences

> SHOW SEQUENCES;

         sequence_schema        |   sequence_name
--------------------------------+---------------------
  public                        | customer_seq
  public                        | desc_customer_list
  pg_temp_1603124728816183000_1 | temp_seq
(3 rows)

Cache sequence values in memory (per node)

For improved performance, use the CACHE keyword to cache sequence values in memory at the node level. By default, all sessions on a node share the same cache. If you want sequence values to be cached per session, see Cache sequence values per session.

For example, to cache 10 sequence values in memory per node:

CREATE SEQUENCE customer_seq_cached CACHE 10;

SHOW CREATE customer_seq_cached;

      table_name      |                                                create_statement
----------------------+------------------------------------------------------------------------------------------------------------------
  customer_seq_cached | CREATE SEQUENCE public.customer_seq_cached MINVALUE 1 MAXVALUE 9223372036854775807 INCREMENT 1 START 1 CACHE 10
(1 row)

You can also use the PER NODE CACHE clause to explicitly cache sequence values in memory at the node level. Since this is the default strategy, it is equivalent to using CACHE.

For example, to cache 10 sequence values per node:

CREATE SEQUENCE customer_seq_node_cached PER NODE CACHE 10;

SHOW CREATE customer_seq_node_cached;

      table_name      |                                                create_statement
----------------------+------------------------------------------------------------------------------------------------------------------
  customer_seq_node_cached | CREATE SEQUENCE public.customer_seq_node_cached MINVALUE 1 MAXVALUE 9223372036854775807 INCREMENT 1 START 1 PER NODE CACHE 10
(1 row)

Cache sequence values per session

To cache sequence values within a single session, use the PER SESSION CACHE clause.

For example, to cache 10 sequence values per session:

CREATE SEQUENCE customer_seq_session_cached PER SESSION CACHE 10;

SHOW CREATE customer_seq_session_cached;

      table_name               |                                                create_statement
-------------------------------+------------------------------------------------------------------------------------------------------------------
  customer_seq_session_cached  | CREATE SEQUENCE public.customer_seq_session_cached MINVALUE 1 MAXVALUE 9223372036854775807 INCREMENT 1 START 1 PER SESSION CACHE 10
(1 row)

See also