深入解讀PostgreSQL中的序列及其相關(guān)函數(shù)的用法
更新時(shí)間：2016年01月05日 14:57:46 作者：David Camp
這篇文章主要介紹了PostgreSQL中的序列及其相關(guān)函數(shù)的用法,包括序列的更新和刪除等重要知識(shí),需要的朋友可以參考下
一、簡(jiǎn)介
序列對(duì)象（也叫序列生成器）就是用CREATE SEQUENCE 創(chuàng)建的特殊的單行表。一個(gè)序列對(duì)象通常用于為行或者表生成唯一的標(biāo)識(shí)符。
二、創(chuàng)建序列
方法一：直接在表中指定字段類型為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 TABLE david=#
方法二：先創(chuàng)建序列名稱，然后在新建的表中列屬性指定序列就可以了，該列需int 類型
創(chuàng)建序列的語(yǔ)法：
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 } ]
實(shí)例：
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=#
三、查看序列
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 | 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)
四、序列應(yīng)用
4.1 在INSERT 命令中使用序列
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 數(shù)據(jù)遷移后更新序列
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 (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
--------- 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;
BEGIN
david=# copy tbl_xulie from '/tmp/tbl_xulie.sql';
COPY 11
david=# select setval('tbl_xulie_id_seq', max(id)) from tbl_xulie;
setval
-------- 25 (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 (12 rows)
david=# select nextval('tbl_xulie_id_seq');
nextval
--------- 27 (1 row)
五、序列函數(shù)
下面序列函數(shù)，為我們從序列對(duì)象中獲取最新的序列值提供了簡(jiǎn)單和并發(fā)讀取安全的方法。
5.1 查看下一個(gè)序列值
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 重置序列
方法一：使用序列函數(shù)
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 tbl_xulie;
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)
效果同a. setval(regclass, bigint)
b.2 setval(regclass, bigint, false)
david=# truncate tbl_xulie;
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)
方法二：修改序列
修改序列的語(yǔ)法：
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
實(shí)例：
david=# truncate tbl_xulie;
TRUNCATE TABLE
david=# 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 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)
六、刪除序列
語(yǔ)法：
DROP SEQUENCE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
當(dāng)有表字段使用到PG序列時(shí)，不能直接刪除。
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=#
說明：對(duì)于序列是由建表時(shí)指定serial 創(chuàng)建的，刪除該表的同時(shí)，對(duì)應(yīng)的序列也會(huì)被刪除。
七、其他說明
a.currval取得的是當(dāng)前會(huì)話的序列值，在當(dāng)前會(huì)話中該值不會(huì)因?yàn)槠渌麜?huì)話取了nextval而變化。會(huì)變化的是全局的last_value值,并且當(dāng)前會(huì)話中如果沒有讀過nextval值時(shí)直接讀currval是會(huì)報(bào)錯(cuò)的。
b.對(duì)于序列是由建表時(shí)指定serial時(shí)創(chuàng)建時(shí)，刪除該表的同時(shí)，對(duì)應(yīng)的序列也會(huì)被刪除。
c.表主鍵數(shù)據(jù)可以用跟表相關(guān)的序列，也可以用其他序列，但不推薦，只是PG默認(rèn)它沒錯(cuò)。
d.為使多用戶并發(fā)下同一個(gè)序列取值不會(huì)重復(fù)，nextval是不會(huì)rollback的，不過可以使用setval重置
如果一個(gè)序列對(duì)象是帶著缺省參數(shù)創(chuàng)建的，那么對(duì)它調(diào)用 nextval 將返回從1 開始的后續(xù)的數(shù)值。 其它的行為可以通過使用 CREATE SEQUENCE 命令里的 特殊參數(shù)獲?。粎㈤喥涿顓⒖柬?yè)獲取更多信息。
e.為了避免從同一個(gè)序列獲取數(shù)值的當(dāng)前事務(wù)被阻塞， nextval 操作決不會(huì)回滾；也就是說，一旦一個(gè)數(shù)值已經(jīng)被抓走， 那么就認(rèn)為它已經(jīng)用過了，即使調(diào)用 nextval 的事務(wù)后面又退出了也一樣。這就意味著退出的事務(wù)可能在序列賦予的數(shù)值中留下"空洞"。 setval 操作也決不回滾。
相關(guān)文章
Postgresql 存儲(chǔ)過程(plpgsql)兩層for循環(huán)的操作
這篇文章主要介紹了Postgresql 存儲(chǔ)過程(plpgsql)兩層for循環(huán)的操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來(lái)看看吧2021-01-01
這篇文章主要介紹了在postgresql中通過命令行執(zhí)行sql文件，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來(lái)看看吧2021-01-01
postgresql 計(jì)算兩點(diǎn)距離的2種方法小結(jié)
這篇文章主要介紹了postgresql 計(jì)算兩點(diǎn)距離的2種方法小結(jié)，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來(lái)看看吧2021-01-01
這篇文章主要介紹了PostgreSQL 對(duì)數(shù)組的遍歷操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來(lái)看看吧2021-01-01
Windows下Postgresql數(shù)據(jù)庫(kù)的下載與配置方法
這篇文章主要介紹了Windows下Postgresql數(shù)據(jù)庫(kù)的下載與配置方法 ,需要的朋友可以參考下2014-06-06
聊聊PostgreSql table和磁盤文件的映射關(guān)系
這篇文章主要介紹了聊聊PostgreSql table和磁盤文件的映射關(guān)系，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來(lái)看看吧2021-01-01
這篇文章主要介紹了Postgresql鎖機(jī)制詳解(表鎖和行鎖)，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來(lái)看看吧2020-12-12
淺談postgresql數(shù)據(jù)庫(kù)varchar、char、text的比較
這篇文章主要介紹了淺談postgresql數(shù)據(jù)庫(kù)varchar、char、text的比較，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來(lái)看看吧2020-12-12
這篇文章主要介紹了PostgreSQL中的COMMENT用法說明，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來(lái)看看吧2021-01-01
Postgresql源碼分析returns?setof函數(shù)oracle管道pipelined
這篇文章主要為大家介紹了Postgresql源碼分析returns?setof函數(shù)oracle管道pipelined，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪2023-01-01