SQL
CREATE SEQUENCE Statement
The `CREATE SEQUENCE` statement creates a new sequence number generator.  
#### Examples {#docs:sql:statements:create_sequence::examples}  
Generate an ascending sequence starting from 1:  
```sql
CREATE SEQUENCE serial;
```  
Generate sequence from a given start number:  
```sql
CREATE SEQUENCE serial START 101;
```  
Generate odd numbers using `INCREMENT BY`:  
```sql
CREATE SEQUENCE serial START WITH 1 INCREMENT BY 2;
```  
Generate a descending sequence starting from 99:  
```sql
CREATE SEQUENCE serial START WITH 99 INCREMENT BY -1 MAXVALUE 99;
```  
By default, cycles are not allowed and will result in error, e.g.:  
```console
Sequence Error: nextval: reached maximum value of sequence "serial" (10)
```  
```sql
CREATE SEQUENCE serial START WITH 1 MAXVALUE 10;
```  
`CYCLE` allows cycling through the same sequence repeatedly:  
```sql
CREATE SEQUENCE serial START WITH 1 MAXVALUE 10 CYCLE;
```  
#### Creating and Dropping Sequences {#docs:sql:statements:create_sequence::creating-and-dropping-sequences}  
Sequences can be created and dropped similarly to other catalogue items.  
Overwrite an existing sequence:  
```sql
CREATE OR REPLACE SEQUENCE serial;
```  
Only create sequence if no such sequence exists yet:  
```sql
CREATE SEQUENCE IF NOT EXISTS serial;
```  
Remove sequence:  
```sql
DROP SEQUENCE serial;
```  
Remove sequence if exists:  
```sql
DROP SEQUENCE IF EXISTS serial;
```  
#### Using Sequences for Primary Keys {#docs:sql:statements:create_sequence::using-sequences-for-primary-keys}  
Sequences can provide an integer primary key for a table. For example:  
```sql
CREATE SEQUENCE id_sequence START 1;
CREATE TABLE tbl (id INTEGER DEFAULT nextval('id_sequence'), s VARCHAR);
INSERT INTO tbl (s) VALUES ('hello'), ('world');
SELECT * FROM tbl;
```  
The script results in the following table:  
| id |   s   |
|---:|-------|
| 1  | hello |
| 2  | world |  
Sequences can also be added using the [`ALTER TABLE` statement](#docs:sql:statements:alter_table). The following example adds an `id` column and fills it with values generated by the sequence:  
```sql
CREATE TABLE tbl (s VARCHAR);
INSERT INTO tbl VALUES ('hello'), ('world');
CREATE SEQUENCE id_sequence START 1;
ALTER TABLE tbl ADD COLUMN id INTEGER DEFAULT nextval('id_sequence');
SELECT * FROM tbl;
```  
This script results in the same table as the previous example.  
#### Selecting the Next Value {#docs:sql:statements:create_sequence::selecting-the-next-value}  
To select the next number from a sequence, use `nextval`:  
```sql
CREATE SEQUENCE serial START 1;
SELECT nextval('serial') AS nextval;
```  
| nextval |
|--------:|
| 1       |  
Using this sequence in an `INSERT` command:  
```sql
INSERT INTO distributors VALUES (nextval('serial'), 'nothing');
```  
#### Selecting the Current Value {#docs:sql:statements:create_sequence::selecting-the-current-value}  
You may also view the current number from the sequence. Note that the `nextval` function must have already been called before calling `currval`, otherwise a Serialization Error (` sequence is not yet defined in this session`) will be thrown.  
```sql
CREATE SEQUENCE serial START 1;
SELECT nextval('serial') AS nextval;
SELECT currval('serial') AS currval;
```  
| currval |
|--------:|
| 1       |  
#### Syntax {#docs:sql:statements:create_sequence::syntax}  
<div id="rrdiagram"></div>  
`CREATE SEQUENCE` creates a new sequence number generator.  
If a schema name is given then the sequence is created in the specified schema. Otherwise it is created in the current schema. Temporary sequences exist in a special schema, so a schema name may not be given when creating a temporary sequence. The sequence name must be distinct from the name of any other sequence in the same schema.  
After a sequence is created, you use the function `nextval` to operate on the sequence.