CREATE SEQUENCE

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.

Required privileges

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

Synopsis

CREATESEQUENCEIFNOTEXISTSsequence_nameNOCYCLEMINVALUEMAXVALUEINCREMENTBYMINVALUEMAXVALUESTARTWITHinteger

Parameters

ParameterDescription
seq_nameThe 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.
INCREMENTThe value by which the sequence is incremented. A negative number creates a descending sequence. A positive number creates an ascending sequence.Default: 1
MINVALUEThe 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
MAXVALUEThe 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
STARTThe first value of the sequence. Default for ascending: 1 Default for descending: -1
NO CYCLECurrently, all sequences are set to NO CYCLE and the sequence will not wrap.

Sequence functions

We support the following SQL sequence functions:

  • nextval('seq_name')

Note:
If nextval() is used in conjunction with RETURNING NOTHING statements, the sequence increments can be reordered. For more information, see Parallel Statement Execution.

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

Examples

List all sequences

  1. > SELECT * FROM information_schema.sequences;
  1. +------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------+
  2. | sequence_catalog | sequence_schema |   sequence_name    | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value |    minimum_value     |    maximum_value    | increment | cycle_option |
  3. +------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------+
  4. | def              | db_2            | test_4             | INT       |                64 |                       2 |             0 |           1 |                    1 | 9223372036854775807 |         1 | NO           |
  5. | def              | test_db         | customer_seq       | INT       |                64 |                       2 |             0 |         101 |                    1 | 9223372036854775807 |         2 | NO           |
  6. | def              | test_db         | desc_customer_list | INT       |                64 |                       2 |             0 |        1000 | -9223372036854775808 |                  -1 |        -2 | NO           |
  7. | def              | test_db         | test_sequence3     | INT       |                64 |                       2 |             0 |           1 |                    1 | 9223372036854775807 |         1 | NO           |
  8. +------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------+
  9. (4 rows)

Create a sequence with default settings

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

  1. > CREATE SEQUENCE customer_seq;
  1. > SHOW CREATE customer_seq;
  1. +--------------+--------------------------------------------------------------------------+
  2. |  table_name  |                             create_statement                             |
  3. +--------------+--------------------------------------------------------------------------+
  4. | customer_seq | CREATE SEQUENCE customer_seq MINVALUE 1 MAXVALUE 9223372036854775807     |
  5. |              | INCREMENT 1 START 1                                                      |
  6. +--------------+--------------------------------------------------------------------------+
  7. (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.

  1. > CREATE SEQUENCE desc_customer_list START -1 INCREMENT -2;
  1. > SHOW CREATE desc_customer_list;
  1. +--------------------+--------------------------------------------------------------------------+
  2. |     table_name     |                             create_statement                             |
  3. +--------------------+--------------------------------------------------------------------------+
  4. | desc_customer_list | CREATE SEQUENCE desc_customer_list MINVALUE -9223372036854775808         |
  5. |                    | MAXVALUE -1 INCREMENT -2 START -1                                        |
  6. +--------------------+--------------------------------------------------------------------------+
  7. (1 row)

Create a table with a sequence

In this example, we create a table using the sequence we created in the first example as the table's primary key.

  1. > CREATE TABLE customer_list (
  2.     id INT PRIMARY KEY DEFAULT nextval('customer_seq'),
  3.     customer string,
  4.     address string
  5.   );

Insert a few records to see the sequence.

  1. > INSERT INTO customer_list (customer, address)
  2.   VALUES
  3.     ('Lauren', '123 Main Street'),
  4.     ('Jesse', '456 Broad Ave'),
  5.     ('Amruta', '9876 Green Parkway');
  1. > SELECT * FROM customer_list;
  1. +----+----------+--------------------+
  2. | id | customer |      address       |
  3. +----+----------+--------------------+
  4. |  1 | Lauren   | 123 Main Street    |
  5. |  2 | Jesse    | 456 Broad Ave      |
  6. |  3 | Amruta   | 9876 Green Parkway |
  7. +----+----------+--------------------+

View the current value of a sequence

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

  1. > SELECT * FROM customer_seq;
  1. +------------+---------+-----------+
  2. | last_value | log_cnt | is_called |
  3. +------------+---------+-----------+
  4. |          3 |       0 |   true    |
  5. +------------+---------+-----------+

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:

  1. > SELECT currval('customer_seq');
  1. +---------+
  2. | currval |
  3. +---------+
  4. |       3 |
  5. +---------+

See also

Was this page helpful?
YesNo