An in-depth interpretation of the use of sequences and their related functions in PostgreSQL _postgresql

First, Introduction

A Sequence object (also called a sequence builder) is a special Single-line table created with the Create SEQUENCE. A sequence object is typically used to generate a unique identifier for a row or table.

Second, create a sequence

Method One: Specify the field type as serial type directly in the table

david=# CREATE TABLE Tbl_xulie (
# ID serial,
David (# Name text);
Notice:create TABLE would CREATE implicit sequence "Tbl_xulie_id_seq" for serial column "Tbl_xulie.id"
create Table
   david=#

Method Two: First create the sequence name, and then in the new Table column properties to specify the sequence, 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}]

Instance:

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_xulie 2_id_seq '),
David (# Name text);
CREATE TABLE
david=# 

Third, 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 Sequence Properties

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   | bigi NT | 9223372036854775807
 min_value |   bigint | 1
 cache_value  | bigint | 1
 log_cnt    | bigint | 0< C23/>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 Using a 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

david=# truncate Tbl_xulie;
TRUNCATE TABLE david=# david=# insert INTO Tbl_xulie values (nextval (' tbl_xulie_id_seq '), ' Sandy ');
Insert 0 1 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 '), ' Eagle ');
Insert 0 1 david=# INSERT into Tbl_xulie values (nextval (' tbl_xulie_id_seq '), ' Miles ');
Insert 0 1 david=# INSERT into Tbl_xulie values (nextval (' tbl_xulie_id_seq '), ' Simon '); 
Insert 0 1 david=# INSERT into Tbl_xulie values (nextval (' tbl_xulie_id_seq '), ' Rock ');
Insert 0 1 david=# INSERT into Tbl_xulie values (nextval (' tbl_xulie_id_seq '), ' Peter ');
Insert 0 1 david=# INSERT into Tbl_xulie values (nextval (' tbl_xulie_id_seq '), ' Sally ');
Insert 0 1 david=# INSERT into Tbl_xulie values (nextval (' tbl_xulie_id_seq '), ' Nicole ');
Insert 0 1 david=# INSERT into Tbl_xulie values (nextval (' tbl_xulie_id_seq '), ' Monica '); Insert 0 1 david=# INSERT into Tbl_xulie values (nextval (' Tbl_xulie_id_seq '), ' Renee ');
 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
(rows)

david=# copy Tbl_xulie to '/tmp/tbl_xulie.sql ';
COPY one
david=# truncate Tbl_xulie;
TRUNCATE TABLE
david=# alter sequence tbl_xulie_id_seq restart with;
ALTER SEQUENCE
david=# Select Currval (' Tbl_xulie_id_seq ');
 Currval 

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

david=# Select Nextval (' Tbl_xulie_id_seq ');
 Nextval 

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

david=# Select Nextval (' Tbl_xulie_id_seq ');

 Nextval 
---------
(1 row)

david=# begin;
BEGIN
david=# copy tbl_xulie from '/tmp/tbl_xulie.sql ';
COPY
-david=# Select Setval (' Tbl_xulie_id_seq ', max (ID)) from Tbl_xulie;
Setval 

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

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
(rows)

david=# Select Nextval (' Tbl_xulie_id_seq ');
 Nextval 

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

Five, sequence functions

The following sequence functions provide a simple and concurrent read security method for getting the latest sequence values from the sequence objects.

5.1 View Next Sequence value

david=# Select Nextval (' Tbl_xulie_id_seq ');
 Nextval 

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

david=# Select Nextval (' Tbl_xulie_id_seq ');
 Nextval 

---------
    4
(1 row)

5.2 View recent values for a sequence

david=# Select Nextval (' Tbl_xulie_id_seq ');
 Nextval 

---------
    4
(1 row)

david=# Select Currval (' Tbl_xulie_id_seq ');
 Currval 

---------
    4
(1 row)

david=# Select Currval (' Tbl_xulie_id_seq ');
 Currval 

---------
    4
(1 row)

5.3 Reset Sequence

Method one: Using Sequence functions

A. Setval (Regclass, bigint)

david=# truncate Tbl_xulie;
TRUNCATE TABLE
david=# Select Setval (' Tbl_xulie_id_seq ', 1);
 Setval 

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

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

 ID | Name 
----+-------
 2 | Sandy
 3 | David
(2 rows)

david=# Select Currval (' Tbl_xulie_id_seq ');
 Currval 

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

david=# Select Nextval (' Tbl_xulie_id_seq ');

 Nextval 
---------
    4
(1 row)

B. Setval (Regclass, bigint, Boolean)

B.1 setval (Regclass, bigint, True) david=# truncate

;
TRUNCATE TABLE
david=# Select Setval (' Tbl_xulie_id_seq ', 1, true);
 Setval 

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

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

 ID | Name 
----+-------
 2 | Sandy
 3 | David
(2 rows)

Effect with a. Setval (Regclass, bigint)

B.2 Setval (Regclass, bigint, false) david=# truncate

;
TRUNCATE TABLE
david=# Select Setval (' Tbl_xulie_id_seq ', 1, false);
 Setval 

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

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

 ID | Name 
----+-------
 1 | Sandy
 2 | David
(2 rows)

Method Two: Modify the sequence

To modify the syntax of a sequence:

ALTER SEQUENCE name [INCREMENT [by] INCREMENT]
  [MinValue minvalue | NO MinValue] [MAXVALUE MAXVALUE | NO MAXVALUE]
  [start [with] start]
  [Restart [[with] restart]]
  [Cache Cache] [[NO] CYCLE]
   [owned by {table.column | NONE]
alter SEQUENCE name OWNER to New_owner
alter SEQUENCE name RENAME to new_name
alter SEQUENCE name SET SCHEMA New_schema

Instance:

david=# truncate Tbl_xulie;
TRUNCATE TABLE
david=# alter sequence TBL_XULIE_ID_SEQ restart with 0;
Error:restart Value (0) cannot is less than MinValue (1)
david=# alter sequence tbl_xulie_id_seq restart with 1;
ALTER SEQUENCE
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)

david=# Select Nextval (' Tbl_xulie_id_seq ');
 Nextval 

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

Vi. Delete Sequence

Grammar:

DROP SEQUENCE [IF EXISTS] name [, ...] [CASCADE | RESTRICT]

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

david=# drop sequence tbl_xulie2_id_seq;
Error:cannot drop sequence Tbl_xulie2_id_seq because other objects depend on it
detail:default for table Tbl_xulie2 Column ID depends on sequence tbl_xulie2_id_seq
hint:use DROP ... CASCADE to drop the dependent objects too.
david=# drop table tbl_xulie2;
Drop TABLE
david=# drop sequence tbl_xulie2_id_seq;
DROP SEQUENCE
david=# 

Note: For a sequence is created by specifying serial when the table is built, the corresponding sequence is deleted while the table is deleted.

The

Seven, other description
a.currval Gets the sequence value of the current session, and the value in the current session does not change because the other session is Nextval. It is the global Last_value value that will change, and the current session will be nextval if the currval is read directly if the value is not read.
B. When a sequence is created by specifying serial when the table is built, the corresponding sequence is deleted as well.
C. Table primary key data can be associated with a table-related sequence, or it can be used in other sequences, but not recommended, just PG default it's right.
D. In order for multiuser concurrency to have a sequence value that is not duplicated, nextval is not rollback, but you can use Setval to reset
If a sequence object was created with a default parameter, calling Nextval on it would return a subsequent number starting at 1. Other behaviors can be obtained by using special parameters in the CREATE SEQUENCE command; see the Command Reference page for more information.
E. The nextval operation will never be rolled back in order to avoid the current transaction that gets the numeric value from the same sequence, that is, once a value has been taken, it is considered to have been used, even after the transaction that invoked Nextval has exited. This means that the exiting transaction may leave a "hole" in the value given by the sequence. The setval operation is never rolled back.