深入解讀PostgreSQL中的序列及其相關函數的用法_PostgreSQL

一、簡介

序列對象（也叫序列產生器）就是用CREATE SEQUENCE 建立的特殊的單行表。一個序列對象通常用於為行或者表產生唯一的標識符。

二、建立序列

方法一：直接在表中指定欄位類型為serial 類型

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=#

方法二：先建立序列名稱，然後在建立的表中列屬性指定序列就可以了，該列需int 類型

建立序列的文法：

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 } ]

執行個體：

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=# 

三、查看序列

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=#

查看序列屬性

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)

四、序列應用

4.1 在INSERT 命令中使用序列

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 資料移轉後更新序列

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)

五、序列函數

下面序列函數，為我們從序列對象中擷取最新的序列值提供了簡單和並發讀取安全的方法。

5.1 查看下一個序列值

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

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

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

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

5.2 查看序列最近使用值

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 重設序列

方法一：使用序列函數

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)

效果同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)

方法二：修改序列

修改序列的文法：

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

執行個體：

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)

六、刪除序列

文法：

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

當有表欄位使用到PG序列時，不能直接刪除。

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=# 

說明：對於序列是由建表時指定serial 建立的，刪除該表的同時，對應的序列也會被刪除。

七、其他說明
a.currval取得的是當前會話的序列值，在當前會話中該值不會因為其他會話取了nextval而變化。會變化的是全域的last_value值,並且當前會話中如果沒有讀過nextval值時直接讀currval是會報錯的。
b.對於序列是由建表時指定serial時建立時，刪除該表的同時，對應的序列也會被刪除。
c.表主鍵資料可以用跟表相關的序列，也可以用其他序列，但不推薦，只是PG預設它沒錯。
d.為使多使用者並發下同一個序列取值不會重複，nextval是不會rollback的，不過可以使用setval重設
如果一個序列對象是帶著預設參數建立的，那麼對它調用 nextval 將返回從1 開始的後續的數值。 其它的行為可以通過使用 CREATE SEQUENCE 命令裡的 特殊參數擷取；參閱其命令參考頁擷取更多資訊。
e.為了避免從同一個序列擷取數值的當前事務被阻塞， nextval 操作決不會復原；也就是說，一旦一個數值已經被抓走， 那麼就認為它已經用過了，即使調用 nextval 的事務後面又退出了也一樣。這就意味著退出的事務可能在序列賦予的數值中留下"空洞"。 setval 操作也決不復原。