Categories:
Table, View, & Sequence DDL

CREATE SEQUENCE

Creates a new sequence, which can be used for generating sequential, unique numbers.

For more details, see Using Sequences.

See also:
ALTER SEQUENCE

Syntax

CREATE [ OR REPLACE ] SEQUENCE [ IF NOT EXISTS ] <name>
  [ WITH ]
  [ START [ WITH ] [ = ] <initial_value> ]
  [ INCREMENT [ BY ] [ = ] <sequence_interval> ]
  [ COMMENT = '<string_literal>' ]

Required Parameters

name

Specifies the identifier for the sequence; must be unique for the schema in which the sequence is created.

In addition, the identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (e.g. "My object"). Identifiers enclosed in double quotes are also case-sensitive.

For more details, see Identifier Syntax.

Optional Parameters

START [ WITH ] [ = ] initial_value

Specifies the first value returned by the sequence. Supported values are any value that can be represented by a 64-bit two’s compliment integer (from -2^63 to 2^63-1).

Default: 1

INCREMENT [ BY ] [ = ] sequence_interval

Specifies the step interval of the sequence:

  • For positive sequence interval n, the next n-1 values are reserved by each sequence call.
  • For negative sequence interval -n, the next n-1 lower values are reserved by each sequence call.

Supported values are any value that can be represented by a 64-bit two’s compliment integer. 0 is not allowed as a sequence interval.

Default: 1

COMMENT = 'string_literal'

Specifies a comment for the sequence.

Default: No value

Usage Notes

  • The first/initial value for a sequence cannot be changed after the sequence is created.

Examples

Here is a simple example of using sequences:

CREATE OR REPLACE SEQUENCE seq_01 START = 1 INCREMENT = 1;
CREATE OR REPLACE TABLE sequence_test_table (i INTEGER);
SELECT seq_01.nextval;

Output:

+---------+
| NEXTVAL |
|---------|
|       1 |
+---------+

Run the same query again; note how the sequence numbers change:

SELECT seq_01.nextval;

Output:

+---------+
| NEXTVAL |
|---------|
|       2 |
+---------+

Now use the sequence while inserting into a table:

INSERT INTO sequence_test_table (i) VALUES (seq_01.nextval);
SELECT i FROM sequence_test_table;

Output:

SELECT i FROM sequence_test_table;
+---+
| I |
|---|
| 3 |
+---+

Create a sequence that increments by 5 rather than by 1:

CREATE OR REPLACE SEQUENCE seq_5 START = 1 INCREMENT = 5;
SELECT seq_5.nextval a, seq_5.nextval b, seq_5.nextval c, seq_5.nextval d;

Output:

+---+---+----+----+
| A | B |  C |  D |
|---+---+----+----|
| 1 | 6 | 11 | 16 |
+---+---+----+----+

Run the same query again; note how the sequence numbers change. You might expect that the next set of sequence numbers would start 5 higher than the previous statement left off. However, the next sequence number starts 20 higher (5 * 4, where 5 is the size of the increment and 4 is the number of NEXTVAL operations in the statement):

SELECT seq_5.nextval a, seq_5.nextval b, seq_5.nextval c, seq_5.nextval d;

Output:

+----+----+----+----+
|  A |  B |  C |  D |
|----+----+----+----|
| 36 | 41 | 46 | 51 |
+----+----+----+----+

This example demonstrates that you can use a sequence as a default value for a column to provide unique identifiers for each row in a table:

CREATE OR REPLACE SEQUENCE seq90;
CREATE OR REPLACE TABLE sequence_demo (i INTEGER DEFAULT seq90.nextval, dummy SMALLINT);
INSERT INTO sequence_demo (dummy) VALUES (0);

-- Keep doubling the number of rows:
INSERT INTO sequence_demo (dummy) SELECT dummy FROM sequence_demo;
INSERT INTO sequence_demo (dummy) SELECT dummy FROM sequence_demo;
INSERT INTO sequence_demo (dummy) SELECT dummy FROM sequence_demo;
INSERT INTO sequence_demo (dummy) SELECT dummy FROM sequence_demo;
INSERT INTO sequence_demo (dummy) SELECT dummy FROM sequence_demo;
INSERT INTO sequence_demo (dummy) SELECT dummy FROM sequence_demo;
INSERT INTO sequence_demo (dummy) SELECT dummy FROM sequence_demo;
INSERT INTO sequence_demo (dummy) SELECT dummy FROM sequence_demo;
INSERT INTO sequence_demo (dummy) SELECT dummy FROM sequence_demo;
INSERT INTO sequence_demo (dummy) SELECT dummy FROM sequence_demo;
SELECT i FROM sequence_demo ORDER BY i LIMIT 10;

Output:

+----+
|  I |
|----|
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
+----+

This shows that we have distinct values for every row:

SELECT COUNT(i), COUNT(DISTINCT i), MIN(i), MAX(i) FROM sequence_demo;

Output:

+----------+-------------------+--------+--------+
| COUNT(I) | COUNT(DISTINCT I) | MIN(I) | MAX(I) |
|----------+-------------------+--------+--------|
|     1024 |              1024 |      1 |   1024 |
+----------+-------------------+--------+--------+

More examples are available in Using Sequences.