Sequence in PostgreSQL

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)

postgres=# alter SEQUENCE t2_id_seq OWNED by none ;
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)