ALTER SEQUENCE

The ALTER SEQUENCE statementchanges the name, increment values, and other settings of a sequence.

Note:

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

Required privileges

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

Synopsis

ALTERSEQUENCEIFEXISTSsequence_nameINCREMENTBYMINVALUEMAXVALUESTARTWITHintegerNOMINVALUEMAXVALUECYCLECYCLE

Parameters

ParameterDescription
IF EXISTSModify the sequence only if it exists; if it does not exist, do not return an error.
sequence_nameThe name of the sequence you want to modify.
INCREMENTThe new value by which the sequence is incremented. A negative number creates a descending sequence. A positive number creates an ascending sequence.
MINVALUEThe new minimum value of the sequence. Default: 1
MAXVALUEThe new maximum value of the sequence. Default: 9223372036854775807
STARTThe value the sequence starts at if you RESTART or if the sequence hits the MAXVALUE and CYCLE is set. RESTART and CYCLE are not implemented yet.
CYCLEThe sequence will wrap around when the sequence value hits the maximum or minimum value. If NO CYCLE is set, the sequence will not wrap.

Examples

Change the increment value of a sequence

In this example, we're going to change the increment value of a sequence from its current state (i.e., 1) to 2.

  1. > ALTER SEQUENCE customer_seq INCREMENT 2;

Next, we'll add another record to the table and check that the new record adheres to the new sequence.

  1. > INSERT INTO customer_list (customer, address) VALUES ('Marie', '333 Ocean Ave');
  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. |  5 | Marie    | 333 Ocean Ave      |
  8. +----+----------+--------------------+

Set the next value of a sequence

In this example, we're going to change the next value of the example sequence (customer_seq). Currently, the next value will be 7 (i.e., 5 + INCREMENT 2). We will change the next value to 20.

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

  1. > SELECT setval('customer_seq', 20, false);
  1. +--------+
  2. | setval |
  3. +--------+
  4. |     20 |
  5. +--------+

Note:

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.

  1. > INSERT INTO customer_list (customer, address) VALUES ('Lola', '333 Schermerhorn');
  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. |  5 | Marie    | 333 Ocean Ave      |
  8. | 20 | Lola     | 333 Schermerhorn   |
  9. +----+----------+--------------------+

See also

Was this page helpful?
YesNo