In-depth interpretation of sequences in PostgreSQL and their related functions, and interpretation of postgresql

In-depth interpretation of sequences in PostgreSQL and their related functions, and interpretation of postgresql

I. Introduction

A sequence object (also called a SEQUENCE generator) is a special single-row table created using create sequence. A sequence object is usually used to generate a unique identifier for a row or table.

Ii. Create a sequence

Method 1: Specify the field type as serial 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 TABLEdavid=#

Method 2: first create a sequence name, and then specify the sequence in the column attribute of the newly created table. This column must be of the int type.

Syntax for sequence creation:

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 SEQUENCEdavid=# david=# create table tbl_xulie2 (david(# id int4 not null default nextval('tbl_xulie2_id_seq'),david(# name text);CREATE TABLEdavid=# 

Iii. View 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 attributes

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 | fOwned 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. Sequential Application

4.1 Use sequence in INSERT command

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

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

4.2 update sequence after data migration

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

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

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

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

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

david=# select nextval('tbl_xulie_id_seq');

 nextval ---------   101(1 row)

david=# begin;BEGINdavid=# copy tbl_xulie from '/tmp/tbl_xulie.sql';COPY 11david=# select setval('tbl_xulie_id_seq', max(id)) from tbl_xulie; setval 

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

david=# end;COMMITdavid=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Flash');INSERT 0 1david=# 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)

V. Sequence Functions

The following sequence functions provide a simple and safe way to obtain the latest sequence values from the sequence objects.

5.1 view the 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 the most recently used Sequence Value

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 1: use sequence Functions

a. setval(regclass, bigint)david=# truncate tbl_xulie;TRUNCATE TABLEdavid=# 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 1david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'David');   INSERT 0 1david=# 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 tbl_xulie;TRUNCATE TABLEdavid=# 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 1david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'David');INSERT 0 1david=# select * from tbl_xulie;

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

The effect is the same as that of a. setval (regclass, bigint)

b.2 setval(regclass, bigint, false)david=# truncate tbl_xulie;TRUNCATE TABLEdavid=# 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 1david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'David');INSERT 0 1david=# select * from tbl_xulie;

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

Method 2: Modify the sequence

Modify the sequence Syntax:

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_ownerALTER SEQUENCE name RENAME TO new_nameALTER SEQUENCE name SET SCHEMA new_schema

Instance:

david=# truncate tbl_xulie;TRUNCATE TABLEdavid=# alter sequence tbl_xulie_id_seq restart with 0;ERROR: RESTART value (0) cannot be less than MINVALUE (1)david=# alter sequence tbl_xulie_id_seq restart with 1;ALTER SEQUENCEdavid=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'David');INSERT 0 1david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Sandy');INSERT 0 1david=# 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

Syntax:

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

When a table field uses the 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 itDETAIL: default for table tbl_xulie2 column id depends on sequence tbl_xulie2_id_seqHINT: Use DROP ... CASCADE to drop the dependent objects too.david=# drop table tbl_xulie2;DROP TABLEdavid=# drop sequence tbl_xulie2_id_seq;DROP SEQUENCEdavid=# 

Note: If the sequence is created by the specified serial when the table is created, the sequence is also deleted when the table is deleted.

VII. Other Instructions
A. currval obtains the Sequence Value of the current session. In the current session, this value does not change because nextval is used by other sessions. The global last_value value is changed. If the nextval value is not read in the current session, an error is returned.
B. When the sequence is created when serial is specified during table creation, the sequence is also deleted when the table is deleted.
C. Table primary key data can use table-related sequences or other sequences, but it is not recommended. It is true for PostgreSQL by default.
D. To prevent repeated values in the same sequence under multi-user concurrency, nextval will not roll back, but it can be reset using setval.
If a sequence object is created with the default parameter, call nextval to return a subsequent value starting from 1. Other behaviors can be obtained by using special parameters in the create sequence command. For more information, see the command reference page.
E. the nextval operation will never roll back to avoid blocking the current transaction for obtaining a value from the same sequence. That is to say, once a value has been taken away, it is deemed to have been used, even if the transaction that calls nextval exits again. This means that the exited transaction may leave "holes" in the values assigned by the sequence ". The setval operation will never be rolled back.

Articles you may be interested in:

• Describes full-text search in PostgreSQL in detail.
• Use Bucardo5 for master database replication of PostgreSQL
• Tutorial on creating a MongoDB copy based on PostgreSQL
• Notes for using array values in PostgreSQL
• How to use Ruby on Rails and PostgreSQL to automatically generate UUID
• Notes for using the date type in PostgreSQL
• Tips for improving PostgreSQL Performance
• Tutorial on implementing recursive query in PostgreSQL
• Tutorial on installing and using the extension module on PostgreSQL
• Introduction to the range type features in PostgreSQL