Sequence In PostgreSQL:
--------------------------
What Is A Sequence?
A sequence is a special kind of database object designed for generating unique numeric identifiers.
It is typically used to generate artificial primary keys. Sequences are similar, but not identical,
to the AUTO_INCREMENT concept in MySQL.
Type 1:
--------
Here created a sequence with all options.
start with 4 --sequence first value will be 4,because it is starting with 4.
increment by 2
minvalue 2
maxvalue 10
cache 2 --it will keep 2 sequence values in memory.
cycle --when ever it reaches maxvalue,then it repeats with minvalue.
owned by all_sequence_options.id --Assigning sequence to particular table,then when ever we dropped a table then sequence also will get dropped off automatically.
EX:
edb=# create table all_sequence_options(id int,name text);
CREATE TABLE
edb=# create SEQUENCE all_seq increment by 2 minvalue 2 maxvalue 10 start with 4 cache 2 cycle owned by all_sequence_options.id;
CREATE SEQUENCE
edb=# \dS+ all_seq
Sequence "enterprisedb.all_seq"
Column | Type | Value | Storage | Description
---------------+---------+---------+---------+-------------
sequence_name | name | all_seq | plain |
last_value | bigint | 4 | plain |
start_value | bigint | 4 | plain |
increment_by | bigint | 2 | plain |
max_value | bigint | 10 | plain |
min_value | bigint | 2 | plain |
cache_value | bigint | 2 | plain |
log_cnt | bigint | 0 | plain |
is_cycled | boolean | t | plain |
is_called | boolean | f | plain |
Type 2:
--------
Create table,Serial keyword on column will create implicit sequence.
Create table seq_table(table_id Serial Not null,name varchar);
EX:
edb=# Create table seq_table(table_id Serial Not null,name varchar);
NOTICE: CREATE TABLE will create implicit sequence "seq_table_table_id_seq" for serial column "seq_table.table_id"
CREATE TABLE
Table:
edb=# \d seq_table
Table "enterprisedb.seq_table"
Column | Type | Modifiers
----------+-------------------+--------------------------------------------------------------
table_id | integer | not null default nextval('seq_table_table_id_seq'::regclass)
name | character varying |
Sequence:
edb=# \dS+ seq_table_table_id_seq
Sequence "enterprisedb.seq_table_table_id_seq"
Column | Type | Value | Storage | Description
---------------+---------+------------------------+---------+-------------
sequence_name | name | seq_table_table_id_seq | plain |
last_value | bigint | 1 | plain |
start_value | bigint | 1 | plain |
increment_by | bigint | 1 | plain |
max_value | bigint | 9223372036854775807 | plain |
min_value | bigint | 1 | plain |
cache_value | bigint | 1 | plain |
log_cnt | bigint | 0 | plain |
is_cycled | boolean | f | plain |
is_called | boolean | f | plain |
edb=#
Restarting sequence:
--------------------
edb=#
edb=# ALTER SEQUENCE seq_table_table_id_seq start with 800 restart;
ALTER SEQUENCE
edb=# \dS+ seq_table_table_id_seq
Sequence "enterprisedb.seq_table_table_id_seq"
Column | Type | Value | Storage | Description
---------------+---------+------------------------+---------+-------------
sequence_name | name | seq_table_table_id_seq | plain |
last_value | bigint | 800 | plain |
start_value | bigint | 800 | plain |
increment_by | bigint | 1 | plain |
max_value | bigint | 9223372036854775807 | plain |
min_value | bigint | 1 | plain |
cache_value | bigint | 1 | plain |
log_cnt | bigint | 0 | plain |
is_cycled | boolean | f | plain |
is_called | boolean | f | plain |
edb=# select nextval('seq_table_table_id_seq');
nextval
---------
801
(1 row)
Sequence with Primary key column :
-------------------------------------
If you create sequence on primary key column,it will avoid the duplicate sequence number entries in table.(if sequence restarts then old values may repeat).
edb=# Create table seq_table(table_id Serial primary key,name varchar);
NOTICE: CREATE TABLE will create implicit sequence "seq_table_table_id_seq" for serial column "seq_table.table_id"
CREATE TABLE
edb=# \d seq_table
Table "enterprisedb.seq_table"
Column | Type | Modifiers
----------+-------------------+--------------------------------------------------------------
table_id | integer | not null default nextval('seq_table_table_id_seq'::regclass)
name | character varying |
Indexes:
"seq_table_pkey" PRIMARY KEY, btree (table_id)
edb=# insert into seq_table(name) values('chiru');
INSERT 0 1
edb=# \dS+ seq_table_table_id_seq
Sequence "enterprisedb.seq_table_table_id_seq"
Column | Type | Value | Storage | Description
---------------+---------+------------------------+---------+-------------
sequence_name | name | seq_table_table_id_seq | plain |
last_value | bigint | 1 | plain |
start_value | bigint | 1 | plain |
increment_by | bigint | 1 | plain |
max_value | bigint | 9223372036854775807 | plain |
min_value | bigint | 1 | plain |
cache_value | bigint | 1 | plain |
log_cnt | bigint | 32 | plain |
is_cycled | boolean | f | plain |
is_called | boolean | t | plain |
edb=# select * from seq_table;
table_id | name
----------+-------
1 | chiru
5 | chiru
6 | chiru
7 | chiru
8 | chiru
9 | chiru
(6 rows)
edb=# select nextval('seq_table_table_id_seq');
nextval
---------
10
edb=# ALTER SEQUENCE seq_table_table_id_seq start with 5 restart;
ALTER SEQUENCE
edb=# \dS+ seq_table_table_id_seq
Sequence "enterprisedb.seq_table_table_id_seq"
Column | Type | Value | Storage | Description
---------------+---------+------------------------+---------+-------------
sequence_name | name | seq_table_table_id_seq | plain |
last_value | bigint | 5 | plain |
start_value | bigint | 5 | plain |
increment_by | bigint | 1 | plain |
max_value | bigint | 9223372036854775807 | plain |
min_value | bigint | 1 | plain |
cache_value | bigint | 1 | plain |
log_cnt | bigint | 0 | plain |
is_cycled | boolean | f | plain |
is_called | boolean | f | plain |
--One sequence number lost(sequence number increased from 10 to 11),Even row is not inserted into table due duplicate value.
edb=# insert into seq_table(name) values('chiru');
ERROR: duplicate key value violates unique constraint "seq_table_pkey"
edb=# select nextval('seq_table_table_id_seq');
nextval
---------
11
(1 row)
Descending sequence:
----------------------
Creating a sequence with minus values.
edb=# create table desc_sequence_table(id int,name text);
CREATE TABLE
edb=# create SEQUENCE desc_seq increment by -1 ;
CREATE SEQUENCE
edb=# \dS+ desc_seq;
Sequence "enterprisedb.desc_seq"
Column | Type | Value | Storage | Description
---------------+---------+----------------------+---------+-------------
sequence_name | name | desc_seq | plain |
last_value | bigint | -1 | plain |
start_value | bigint | -1 | plain |
increment_by | bigint | -1 | plain |
max_value | bigint | -1 | plain |
min_value | bigint | -9223372036854775807 | plain |
cache_value | bigint | 1 | plain |
log_cnt | bigint | 0 | plain |
is_cycled | boolean | f | plain |
is_called | boolean | f | plain |
edb=# insert into desc_sequence_table values(nextval('desc_seq'),'chiru');
INSERT 0 1
edb=# select * from desc_sequence_table;
id | name
----+-------
-3 | chiru
(1 row)
edb=# select nextval('desc_seq');
nextval
---------
-4
How to detach a sequence from a table:
----------------------------------------------------
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+-----------+----------+----------
public | t2 | table | postgres
public | t2_id_seq | sequence | postgres
(2 rows)
ALTER SEQUENCE
postgres=# drop table t2;
DROP TABLE
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+-----------+----------+----------
public | t2_id_seq | sequence | postgres
(1 rows)
