Product Documentation

Appearance

CREATE SEQUENCE ​

Create a sequence number generator.

CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] name [ START [ WITH ] number ]
TEMP | TEMPORARY
Create a temporary sequence that is dropped at the end of the session.
IF NOT EXISTS
Do not return an error if a sequence (or another object) with the same name already exists.
name
Unique sequence name within a schema. Sequence names may be reused across schemas. A sequence may not have the same name as a table if both objects are in the same schema. The sequence name may be database- and schema-qualified. For example: premdb.public.matchkey
START WITH number
Starting number for the generated sequence values. Must be a positive number in the range (1 to 9223372036854774784). The maximum value a sequence can return is 9223372036854775807 (263 -1).

Usage Notes ​

Sequence values increment by 1024 (or greater) on each worker node. See Generating Values with Sequences.

After creating sequences, you can use the \ds command in ybsql to list them. Sequences are also included alongside tables and views in the \d output.

premdb=# \ds
          List of relations
 Schema |  Name   |   Type   |  Owner  
--------+---------+----------+---------
 public | matchid | sequence | brumsby
(1 row)

Examples ​

Create a sequence and select its next value:

premdb=> create sequence matchid;
CREATE SEQUENCE
premdb=> select nextval('matchid') from sys.const;
 nextval 
---------
   1024
(1 row)

Create a table called matchkey and generate values for the matchid column:

premdb=# create table matchkey as 
select seasonid,matchday,htid,atid,ftscore,htscore,
nextval('matchid') as matchid 
from match;
SELECT 8606
premdb=# select * from matchkey limit 10;
 seasonid |      matchday       | htid | atid | ftscore | htscore | matchid 
----------+---------------------+------+------+---------+---------+---------
      15 | 2006-10-28 00:00:00 |    8 |   75 | 0-4     | 0-2     | 1573888
      15 | 2006-09-16 00:00:00 |    8 |   76 | 0-0     | 0-0     | 1574912
      15 | 2006-12-26 00:00:00 |    8 |   77 | 2-1     | 1-1     | 1575936
      15 | 2006-12-30 00:00:00 |    8 |   81 | 3-2     | 2-1     | 1576960
      15 | 2007-04-21 00:00:00 |    8 |   83 | 1-3     | 0-0     | 1577984
      15 | 2007-03-31 00:00:00 |    8 |   84 | 1-0     | 0-0     | 1579008
      15 | 2006-08-19 00:00:00 |    8 |   91 | 2-0     | 2-0     | 1580032
      15 | 2006-09-09 00:00:00 |    8 |   92 | 1-0     | 0-0     | 1581056
      15 | 2006-12-09 00:00:00 |    8 |   94 | 4-0     | 1-0     | 1582080
      15 | 2006-11-04 00:00:00 |    8 |   95 | 0-1     | 0-0     | 1583104
(10 rows)

Create a sequence with a start value and select its next value:

premdb=# drop sequence if exists matchid;
DROP SEQUENCE
premdb=# create sequence matchid start 5000000000;
CREATE SEQUENCE
premdb=# select nextval('matchid') from sys.const;
  nextval   
------------
 5000000512
(1 row)

See also Loading Generated Key Values.

Parent topic:SQL Commands