Of Stack

Read in depth the use of sequences and their related functions in PostgreSQL

  • 2020-05-06 11:52:20
  • OfStack

Introduction

Sequence objects (also known as sequence generators) are special single-row tables created with CREATE SEQUENCE. A sequence object is often used to generate unique identifiers for rows or tables.

ii. Create the sequence

Method 1: specify the field type serial type

directly in the table 

david=# create table tbl_xulie (
david(# id serial,
david(# name text);
NOTICE: CREATE TABLE will create implicit sequence "tbl_xulie_id_seq" for serial column "tbl_xulie.id"
CREATE TABLE
david=#

Method 2: create the sequence name first, and then specify the sequence in the column attribute in the new table. The column needs int type

Syntax for creating a sequence: 


CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
  [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
  [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
  [ OWNED BY { table.column | NONE } ]

Example: 


david=# create sequence tbl_xulie2_id_seq increment by 1 minvalue 1 no maxvalue start with 1;   
CREATE SEQUENCE
david=# 
david=# create table tbl_xulie2 (
david(# id int4 not null default nextval('tbl_xulie2_id_seq'),
david(# name text);
CREATE TABLE
david=#

3. View the sequence 


david=# \d tbl_xulie
             Table "public.tbl_xulie"
 Column | Type  |            Modifiers            
--------+---------+--------------------------------------------------------
 id   | integer | not null default nextval('tbl_xulie_id_seq'::regclass)
 name  | text  | 

david=# \d tbl_xulie2
             Table "public.tbl_xulie2"
 Column | Type  |            Modifiers            
--------+---------+---------------------------------------------------------
 id   | integer | not null default nextval('tbl_xulie2_id_seq'::regclass)
 name  | text  | 

david=#

View the sequence property 


david=# \d tbl_xulie_id_seq
   

 Sequence "public.tbl_xulie_id_seq"


 Column   | Type  |    Value    
---------------+---------+---------------------
 sequence_name | name  | tbl_xulie_id_seq
 last_value  | bigint | 1
 start_value  | bigint | 1
 increment_by | bigint | 1
 max_value   | bigint | 9223372036854775807
 min_value   | bigint | 1
 cache_value  | bigint | 1
 log_cnt    | bigint | 0
 is_cycled   | boolean | f
 is_called   | boolean | f
Owned by: public.tbl_xulie.id



david=# select * from tbl_xulie2_id_seq;


  sequence_name  | last_value | start_value | increment_by |   max_value   | min_value | cache_value | log_cnt | is_cycled | is_called 
-------------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 tbl_xulie2_id_seq |     1 |      1 |      1 | 9223372036854775807 |     1 |      1 |    0 | f     | f
(1 row)

iv. Sequence application

4.1 USES the sequence

in the INSERT command 

david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'David');   
INSERT 0 1
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Sandy');
INSERT 0 1
david=# select * from tbl_xulie;


 id | name 
----+-------
 1 | David
 2 | Sandy
(2 rows)

4.2 update sequence

after data migration 

CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
  [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
  [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
  [ OWNED BY { table.column | NONE } ]
0

  


id | name 
----+--------
 15 | Sandy
 16 | David
 17 | Eagle
 18 | Miles
 19 | Simon
 20 | Rock
 21 | Peter
 22 | Sally
 23 | Nicole
 24 | Monica
 25 | Renee
(11 rows)


david=# copy tbl_xulie to '/tmp/tbl_xulie.sql';
COPY 11
david=# truncate tbl_xulie;
TRUNCATE TABLE
david=# alter sequence tbl_xulie_id_seq restart with 100;
ALTER SEQUENCE
david=# select currval('tbl_xulie_id_seq');
 currval 



CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
  [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
  [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
  [ OWNED BY { table.column | NONE } ]
3 

CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
  [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
  [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
  [ OWNED BY { table.column | NONE } ]
4 

CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
  [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
  [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
  [ OWNED BY { table.column | NONE } ]
5 

CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
  [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
  [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
  [ OWNED BY { table.column | NONE } ]
6 

CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
  [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
  [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
  [ OWNED BY { table.column | NONE } ]
7 

CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
  [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
  [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
  [ OWNED BY { table.column | NONE } ]
8 

CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
  [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
  [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
  [ OWNED BY { table.column | NONE } ]
9 

david=# end;
COMMIT
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Flash');
INSERT 0 1
david=# select * from tbl_xulie;

  


id | name 
----+--------
 15 | Sandy
 16 | David
 17 | Eagle
 18 | Miles
 19 | Simon
 20 | Rock
 21 | Peter
 22 | Sally
 23 | Nicole
 24 | Monica
 25 | Renee
 26 | Flash
(12 rows)


david=# select nextval('tbl_xulie_id_seq');
 nextval 


---------
   27
(1 row)

5. Sequence function

The following sequence function provides a simple and safe way to get the latest sequence value from the sequence object.

5.1 view the next sequence value 


david=# select nextval('tbl_xulie_id_seq');
 nextval 


---------
    3
(1 row)



david=# create sequence tbl_xulie2_id_seq increment by 1 minvalue 1 no maxvalue start with 1;   
CREATE SEQUENCE
david=# 
david=# create table tbl_xulie2 (
david(# id int4 not null default nextval('tbl_xulie2_id_seq'),
david(# name text);
CREATE TABLE
david=#
6 

david=# create sequence tbl_xulie2_id_seq increment by 1 minvalue 1 no maxvalue start with 1;   
CREATE SEQUENCE
david=# 
david=# create table tbl_xulie2 (
david(# id int4 not null default nextval('tbl_xulie2_id_seq'),
david(# name text);
CREATE TABLE
david=#
7

5.2 view the most recent use of the value 


david=# create sequence tbl_xulie2_id_seq increment by 1 minvalue 1 no maxvalue start with 1;   
CREATE SEQUENCE
david=# 
david=# create table tbl_xulie2 (
david(# id int4 not null default nextval('tbl_xulie2_id_seq'),
david(# name text);
CREATE TABLE
david=#
8 

david=# create sequence tbl_xulie2_id_seq increment by 1 minvalue 1 no maxvalue start with 1;   
CREATE SEQUENCE
david=# 
david=# create table tbl_xulie2 (
david(# id int4 not null default nextval('tbl_xulie2_id_seq'),
david(# name text);
CREATE TABLE
david=#
9 

david=# select currval('tbl_xulie_id_seq');
 currval 


david=# \d tbl_xulie
             Table "public.tbl_xulie"
 Column | Type  |            Modifiers            
--------+---------+--------------------------------------------------------
 id   | integer | not null default nextval('tbl_xulie_id_seq'::regclass)
 name  | text  | 

david=# \d tbl_xulie2
             Table "public.tbl_xulie2"
 Column | Type  |            Modifiers            
--------+---------+---------------------------------------------------------
 id   | integer | not null default nextval('tbl_xulie2_id_seq'::regclass)
 name  | text  | 

david=#
1 

david=# \d tbl_xulie
             Table "public.tbl_xulie"
 Column | Type  |            Modifiers            
--------+---------+--------------------------------------------------------
 id   | integer | not null default nextval('tbl_xulie_id_seq'::regclass)
 name  | text  | 

david=# \d tbl_xulie2
             Table "public.tbl_xulie2"
 Column | Type  |            Modifiers            
--------+---------+---------------------------------------------------------
 id   | integer | not null default nextval('tbl_xulie2_id_seq'::regclass)
 name  | text  | 

david=#
2 

david=# \d tbl_xulie
             Table "public.tbl_xulie"
 Column | Type  |            Modifiers            
--------+---------+--------------------------------------------------------
 id   | integer | not null default nextval('tbl_xulie_id_seq'::regclass)
 name  | text  | 

david=# \d tbl_xulie2
             Table "public.tbl_xulie2"
 Column | Type  |            Modifiers            
--------+---------+---------------------------------------------------------
 id   | integer | not null default nextval('tbl_xulie2_id_seq'::regclass)
 name  | text  | 

david=#
3

5.3 reset sequence

Method 1: use the sequence function 


david=# \d tbl_xulie
             Table "public.tbl_xulie"
 Column | Type  |            Modifiers            
--------+---------+--------------------------------------------------------
 id   | integer | not null default nextval('tbl_xulie_id_seq'::regclass)
 name  | text  | 

david=# \d tbl_xulie2
             Table "public.tbl_xulie2"
 Column | Type  |            Modifiers            
--------+---------+---------------------------------------------------------
 id   | integer | not null default nextval('tbl_xulie2_id_seq'::regclass)
 name  | text  | 

david=#
4 

david=# \d tbl_xulie
             Table "public.tbl_xulie"
 Column | Type  |            Modifiers            
--------+---------+--------------------------------------------------------
 id   | integer | not null default nextval('tbl_xulie_id_seq'::regclass)
 name  | text  | 

david=# \d tbl_xulie2
             Table "public.tbl_xulie2"
 Column | Type  |            Modifiers            
--------+---------+---------------------------------------------------------
 id   | integer | not null default nextval('tbl_xulie2_id_seq'::regclass)
 name  | text  | 

david=#
5 

david=# \d tbl_xulie
             Table "public.tbl_xulie"
 Column | Type  |            Modifiers            
--------+---------+--------------------------------------------------------
 id   | integer | not null default nextval('tbl_xulie_id_seq'::regclass)
 name  | text  | 

david=# \d tbl_xulie2
             Table "public.tbl_xulie2"
 Column | Type  |            Modifiers            
--------+---------+---------------------------------------------------------
 id   | integer | not null default nextval('tbl_xulie2_id_seq'::regclass)
 name  | text  | 

david=#
6 

david=# \d tbl_xulie
             Table "public.tbl_xulie"
 Column | Type  |            Modifiers            
--------+---------+--------------------------------------------------------
 id   | integer | not null default nextval('tbl_xulie_id_seq'::regclass)
 name  | text  | 

david=# \d tbl_xulie2
             Table "public.tbl_xulie2"
 Column | Type  |            Modifiers            
--------+---------+---------------------------------------------------------
 id   | integer | not null default nextval('tbl_xulie2_id_seq'::regclass)
 name  | text  | 

david=#
7 

david=# \d tbl_xulie
             Table "public.tbl_xulie"
 Column | Type  |            Modifiers            
--------+---------+--------------------------------------------------------
 id   | integer | not null default nextval('tbl_xulie_id_seq'::regclass)
 name  | text  | 

david=# \d tbl_xulie2
             Table "public.tbl_xulie2"
 Column | Type  |            Modifiers            
--------+---------+---------------------------------------------------------
 id   | integer | not null default nextval('tbl_xulie2_id_seq'::regclass)
 name  | text  | 

david=#
8 

---------
    3
(1 row)



david=# \d tbl_xulie_id_seq
0 

david=# \d tbl_xulie_id_seq
1 

david=# \d tbl_xulie_id_seq
2 

david=# \d tbl_xulie_id_seq
3 

david=# \d tbl_xulie_id_seq
4 

david=# \d tbl_xulie_id_seq
5
The effect is the same as a. setval(regclass, bigint)

david=# \d tbl_xulie_id_seq
6 

david=# \d tbl_xulie_id_seq
7 

david=# \d tbl_xulie_id_seq
8 

david=# \d tbl_xulie_id_seq
9

Method 2: modify the sequence

Modify the syntax of the sequence: 


 Sequence "public.tbl_xulie_id_seq"


 Column   | Type  |    Value    
---------------+---------+---------------------
 sequence_name | name  | tbl_xulie_id_seq
 last_value  | bigint | 1
 start_value  | bigint | 1
 increment_by | bigint | 1
 max_value   | bigint | 9223372036854775807
 min_value   | bigint | 1
 cache_value  | bigint | 1
 log_cnt    | bigint | 0
 is_cycled   | boolean | f
 is_called   | boolean | f
Owned by: public.tbl_xulie.id
0

Example: 


 Sequence "public.tbl_xulie_id_seq"


 Column   | Type  |    Value    
---------------+---------+---------------------
 sequence_name | name  | tbl_xulie_id_seq
 last_value  | bigint | 1
 start_value  | bigint | 1
 increment_by | bigint | 1
 max_value   | bigint | 9223372036854775807
 min_value   | bigint | 1
 cache_value  | bigint | 1
 log_cnt    | bigint | 0
 is_cycled   | boolean | f
 is_called   | boolean | f
Owned by: public.tbl_xulie.id
1

  


 Sequence "public.tbl_xulie_id_seq"


 Column   | Type  |    Value    
---------------+---------+---------------------
 sequence_name | name  | tbl_xulie_id_seq
 last_value  | bigint | 1
 start_value  | bigint | 1
 increment_by | bigint | 1
 max_value   | bigint | 9223372036854775807
 min_value   | bigint | 1
 cache_value  | bigint | 1
 log_cnt    | bigint | 0
 is_cycled   | boolean | f
 is_called   | boolean | f
Owned by: public.tbl_xulie.id
2 

 Sequence "public.tbl_xulie_id_seq"


 Column   | Type  |    Value    
---------------+---------+---------------------
 sequence_name | name  | tbl_xulie_id_seq
 last_value  | bigint | 1
 start_value  | bigint | 1
 increment_by | bigint | 1
 max_value   | bigint | 9223372036854775807
 min_value   | bigint | 1
 cache_value  | bigint | 1
 log_cnt    | bigint | 0
 is_cycled   | boolean | f
 is_called   | boolean | f
Owned by: public.tbl_xulie.id
3 

 Sequence "public.tbl_xulie_id_seq"


 Column   | Type  |    Value    
---------------+---------+---------------------
 sequence_name | name  | tbl_xulie_id_seq
 last_value  | bigint | 1
 start_value  | bigint | 1
 increment_by | bigint | 1
 max_value   | bigint | 9223372036854775807
 min_value   | bigint | 1
 cache_value  | bigint | 1
 log_cnt    | bigint | 0
 is_cycled   | boolean | f
 is_called   | boolean | f
Owned by: public.tbl_xulie.id
4

6. Delete the sequence

Grammar: 


 Sequence "public.tbl_xulie_id_seq"


 Column   | Type  |    Value    
---------------+---------+---------------------
 sequence_name | name  | tbl_xulie_id_seq
 last_value  | bigint | 1
 start_value  | bigint | 1
 increment_by | bigint | 1
 max_value   | bigint | 9223372036854775807
 min_value   | bigint | 1
 cache_value  | bigint | 1
 log_cnt    | bigint | 0
 is_cycled   | boolean | f
 is_called   | boolean | f
Owned by: public.tbl_xulie.id
5

When a table field is used in an PG sequence, it cannot be deleted directly. 


 Sequence "public.tbl_xulie_id_seq"


 Column   | Type  |    Value    
---------------+---------+---------------------
 sequence_name | name  | tbl_xulie_id_seq
 last_value  | bigint | 1
 start_value  | bigint | 1
 increment_by | bigint | 1
 max_value   | bigint | 9223372036854775807
 min_value   | bigint | 1
 cache_value  | bigint | 1
 log_cnt    | bigint | 0
 is_cycled   | boolean | f
 is_called   | boolean | f
Owned by: public.tbl_xulie.id
6

Note: if the sequence is created by serial specified in the table, the corresponding sequence will be deleted when the table is deleted.

vii. Other instructions
a.currval takes the sequence value of the current session, which will not change in the current session because other sessions have taken nextval. What changes is the global last_value value, and reading currval without reading the nextval value in the current session will result in an error.
b. When a sequence is created by specifying serial when the table is built, the corresponding sequence will be deleted when the table is deleted.
c. Table primary key data can be either in a table-related sequence or in other sequences, but it is not recommended, except that PG is correct by default.
d. nextval does not rollback in order for the same sequence to be repeated under multi-user concurrency, but setval can be used to reset
If a sequence object is created with default parameters, calling nextval on it returns subsequent values starting at 1. Other behavior can be obtained by using special parameters in the CREATE SEQUENCE command; See its command reference page for more information.
e. The nextval operation never rolls back in order to avoid the current transaction that gets the value from the same sequence being blocked. That is, once a value has been grabbed, it is considered to have been used, even if the transaction calling nextval exits later. This means that the outgoing transaction may leave a "void" in the value assigned by the sequence. The setval operation is also never rolled back.

Related articles: