PostgreSQL Create Sequence

What is a Sequence ?

Sequence is a database object which used to generate unique identifiers for rows of a table. In general Sequence maintains the uniqueness of the row within the table. All major databases support sequence object

CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] name [ AS data_type ] [ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] [ OWNED BY { table_name.column_name |NONE } ]

Example :-

postgres=# create sequence test_seq;
CREATE SEQUENCE
postgres=# \d test_seq
                          Sequence "public.test_seq"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache 
--------+-------+---------+---------------------+-----------+---------+-------
 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1

A simple “create sequence sequence_name” command will create a sequence start with 1, minimum 1, increment by 1, max value as 9223372036854775807 (bigint value), no cycles, cache 1.

All other clauses are optional

TEMPORARY or TEMP

TEMPORARY or TEMP will create a sequence for this session only, and is automatically dropped onsession exit.

IF NOT EXISTS

Do not throw an error if a relation/object with the same name already exists.

AS DATA_TYPE

The optional clause AS data_type specifies the data type of the sequence. Valid types aresmallint, integer, and bigint. bigint is the default.

INCREMENT

The optional clause INCREMENT BY increment specifies which value is added to the current sequencevalue to create a new value. A positive value will make an ascending sequence, a negative one a descending sequence. The default value is 1.

MINVALUE or NO MINVALUE

The optional clause MINVALUE minvalue determines the minimum value a sequence can generate. If this clause is not supplied or NO MINVALUE is specified, then defaults will be used. The default for an ascending sequence is 1. The default for a descending sequence is the minimum value of the data type.

MAXVALUE or NO MAXVALUE

The optional clause MAXVALUE maxvalue determines the maximum value for the sequence. If this clause is not supplied or NO MAXVALUE is specified, then default values will be used. The default for an ascending sequence is the maximum value of the data type.The default for a descending sequence is -1.

START

The optional clause START WITH start allows the sequence to begin anywhere. The default starting value is minvalue for ascending sequences and maxvalue for descending ones.

CACHE

The optional clause CACHE cache specifies how many sequence numbers are to be preallocated and stored in memory for faster access. The minimum value is 1 (only one value can be generated at a time, i.e., no cache), and this is also the default.

CYCLE or NO CYCLE

The CYCLE option allows the sequence to wrap around when the maxvalue or minvalue has been reached by an ascending or descending sequence respectively. If the limit is reached, the next number generated will be the minvalue or maxvalue, respectively.

If NO CYCLE is specified, any calls to nextval after the sequence has reached its maximum value willn return an error. If neither CYCLE or NO CYCLE are specified, NO CYCLE is the default. OWNED BY table_name.column_name

OWNED BY NONE

The OWNED BY option causes the sequence to be associated with a specific table column, such that if that column (or its whole table) is dropped, the sequence will be automatically dropped as well. The specified table must have the same owner and be in the same schema as the sequence. OWNED BY NONE, the default, specifies that there is no such association Some more examples

Some more examples

postgres=# CREATE  TEMPORARY  SEQUENCE  IF NOT EXISTS  emp_seq 
postgres-# AS int  INCREMENT  BY 1  MINVALUE 1  MAXVALUE 1000  
postgres-# START  WITH 1  CACHE 1  NO CYCLE;
CREATE SEQUENCE
postgres=# \d emp_seq
                   Sequence "pg_temp_3.emp_seq"
  Type   | Start | Minimum | Maximum | Increment | Cycles? | Cache 
---------+-------+---------+---------+-----------+---------+-------
 integer |     1 |       1 |    1000 |         1 | no      |     1

sequence with decreasing value

postgres=# CREATE  SEQUENCE  IF NOT EXISTS  emp_seq1 
postgres-# AS int  INCREMENT  BY -1  MINVALUE 1  MAXVALUE 1000  
postgres-# START  WITH 1000  ;
CREATE SEQUENCE
postgres=# \d emp_seq;
                   Sequence "pg_temp_3.emp_seq"
  Type   | Start | Minimum | Maximum | Increment | Cycles? | Cache 
---------+-------+---------+---------+-----------+---------+-------
 integer |     1 |       1 |    1000 |         1 | no      |     1

               
postgres=# select nextval('emp_seq1');
 nextval 
---------
    1000
(1 row)

postgres=# select nextval('emp_seq1');
 nextval 
---------
     999
(1 row)

Use DROP SEQUENCE to remove a sequence

postgres=# drop sequence emp_seq1;
DROP SEQUENCE

Please refer PostgreSQL sequence functions and operators

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: