CREATE SEQUENCE

On this page

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

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. For more information, see Unique ID best practices.
• 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 discourage indexing on sequential keys. If a table must be indexed on sequential keys, use hash-sharded indexes. Hash-sharded indexes distribute sequential traffic uniformly across ranges, eliminating single-range hot spots and improving write performance on sequentially-keyed indexes at a small cost to read performance.
• By default, you cannot create sequences that are owned by columns in tables in other databases. You can enable such sequence creation by setting the sql.cross_db_sequence_owners.enabled cluster setting to true.

Required privileges

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

Synopsis

Parameters

Sequence functions

CockroachDB supports the following SQL sequence functions:

• 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 temporary tables.

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.

Usage

To create a temporary sequence, add TEMP/TEMPORARY 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 create a table, using the nextval() function for a default value, 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 setval() function. Currently, the next value will be 3 (i.e., 2 + INCREMENT 1). We will change the next value to 5.

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

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

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

For improved performance, use the CACHE keyword to cache sequence values in memory.

For example, to cache 10 sequence values in memory:

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

See also

• ALTER SEQUENCE
• DROP SEQUENCE
• SHOW CREATE
• SHOW SEQUENCES
• Functions and Operators
• SQL Statements
• Online Schema Changes

Was this helpful?