深入解读PostgreSQL中的序列及其相关函数的用法
一、简介
序列对象（也叫序列生成器）就是用CREATESEQUENCE创建的特殊的单行表。一个序列对象通常用于为行或者表生成唯一的标识符。
二、创建序列
方法一：直接在表中指定字段类型为serial类型
david=#createtabletbl_xulie( david(#idserial, david(#nametext); NOTICE:CREATETABLEwillcreateimplicitsequence"tbl_xulie_id_seq"forserialcolumn"tbl_xulie.id" CREATETABLE david=#
方法二：先创建序列名称，然后在新建的表中列属性指定序列就可以了，该列需int类型
创建序列的语法：
CREATE[TEMPORARY|TEMP]SEQUENCEname[INCREMENT[BY]increment]
[MINVALUEminvalue|NOMINVALUE][MAXVALUEmaxvalue|NOMAXVALUE]
[START[WITH]start][CACHEcache][[NO]CYCLE]
[OWNEDBY{table.column|NONE}]
实例：
david=#createsequencetbl_xulie2_id_seqincrementby1minvalue1nomaxvaluestartwith1;
CREATESEQUENCE
david=#
david=#createtabletbl_xulie2(
david(#idint4notnulldefaultnextval('tbl_xulie2_id_seq'),
david(#nametext);
CREATETABLE
david=#
三、查看序列
david=#\dtbl_xulie
Table"public.tbl_xulie"
Column|Type|Modifiers
--------+---------+--------------------------------------------------------
id|integer|notnulldefaultnextval('tbl_xulie_id_seq'::regclass)
name|text|
david=#\dtbl_xulie2
Table"public.tbl_xulie2"
Column|Type|Modifiers
--------+---------+---------------------------------------------------------
id|integer|notnulldefaultnextval('tbl_xulie2_id_seq'::regclass)
name|text|
david=#
查看序列属性
david=#\dtbl_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 Ownedby:public.tbl_xulie.id
david=#select*fromtbl_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 (1row)
四、序列应用
4.1在INSERT命令中使用序列
david=#insertintotbl_xulievalues(nextval('tbl_xulie_id_seq'),'David');
INSERT01
david=#insertintotbl_xulievalues(nextval('tbl_xulie_id_seq'),'Sandy');
INSERT01
david=#select*fromtbl_xulie;
id|name ----+------- 1|David 2|Sandy (2rows)
4.2数据迁移后更新序列
david=#truncatetbl_xulie;
TRUNCATETABLE
david=#
david=#insertintotbl_xulievalues(nextval('tbl_xulie_id_seq'),'Sandy');
INSERT01
david=#insertintotbl_xulievalues(nextval('tbl_xulie_id_seq'),'David');
INSERT01
david=#insertintotbl_xulievalues(nextval('tbl_xulie_id_seq'),'Eagle');
INSERT01
david=#insertintotbl_xulievalues(nextval('tbl_xulie_id_seq'),'Miles');
INSERT01
david=#insertintotbl_xulievalues(nextval('tbl_xulie_id_seq'),'Simon');
INSERT01
david=#insertintotbl_xulievalues(nextval('tbl_xulie_id_seq'),'Rock');
INSERT01
david=#insertintotbl_xulievalues(nextval('tbl_xulie_id_seq'),'Peter');
INSERT01
david=#insertintotbl_xulievalues(nextval('tbl_xulie_id_seq'),'Sally');
INSERT01
david=#insertintotbl_xulievalues(nextval('tbl_xulie_id_seq'),'Nicole');
INSERT01
david=#insertintotbl_xulievalues(nextval('tbl_xulie_id_seq'),'Monica');
INSERT01
david=#insertintotbl_xulievalues(nextval('tbl_xulie_id_seq'),'Renee');
INSERT01
david=#select*fromtbl_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 (11rows)
david=#copytbl_xulieto'/tmp/tbl_xulie.sql';
COPY11
david=#truncatetbl_xulie;
TRUNCATETABLE
david=#altersequencetbl_xulie_id_seqrestartwith100;
ALTERSEQUENCE
david=#selectcurrval('tbl_xulie_id_seq');
currval
--------- 25 (1row)
david=#selectnextval('tbl_xulie_id_seq');
nextval
--------- 100 (1row)
david=#selectnextval('tbl_xulie_id_seq');
nextval --------- 101 (1row)
david=#begin;
BEGIN
david=#copytbl_xuliefrom'/tmp/tbl_xulie.sql';
COPY11
david=#selectsetval('tbl_xulie_id_seq',max(id))fromtbl_xulie;
setval
-------- 25 (1row)
david=#end;
COMMIT
david=#insertintotbl_xulievalues(nextval('tbl_xulie_id_seq'),'Flash');
INSERT01
david=#select*fromtbl_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 (12rows)
david=#selectnextval('tbl_xulie_id_seq');
nextval
--------- 27 (1row)
五、序列函数
下面序列函数，为我们从序列对象中获取最新的序列值提供了简单和并发读取安全的方法。
5.1查看下一个序列值
david=#selectnextval('tbl_xulie_id_seq');
nextval
--------- 3 (1row)
david=#selectnextval('tbl_xulie_id_seq');
nextval
--------- 4 (1row)
5.2查看序列最近使用值
david=#selectnextval('tbl_xulie_id_seq');
nextval
--------- 4 (1row)
david=#selectcurrval('tbl_xulie_id_seq');
currval
--------- 4 (1row)
david=#selectcurrval('tbl_xulie_id_seq');
currval
--------- 4 (1row)
5.3重置序列
方法一：使用序列函数
a.setval(regclass,bigint)
david=#truncatetbl_xulie;
TRUNCATETABLE
david=#selectsetval('tbl_xulie_id_seq',1);
setval
-------- 1 (1row)
david=#insertintotbl_xulievalues(nextval('tbl_xulie_id_seq'),'Sandy');
INSERT01
david=#insertintotbl_xulievalues(nextval('tbl_xulie_id_seq'),'David');
INSERT01
david=#select*fromtbl_xulie;
id|name ----+------- 2|Sandy 3|David (2rows)
david=#selectcurrval('tbl_xulie_id_seq');
currval
--------- 3 (1row)
david=#selectnextval('tbl_xulie_id_seq');
nextval --------- 4 (1row)
b.setval(regclass,bigint,boolean)
b.1setval(regclass,bigint,true)
david=#truncatetbl_xulie;
TRUNCATETABLE
david=#selectsetval('tbl_xulie_id_seq',1,true);
setval
-------- 1 (1row)
david=#insertintotbl_xulievalues(nextval('tbl_xulie_id_seq'),'Sandy');
INSERT01
david=#insertintotbl_xulievalues(nextval('tbl_xulie_id_seq'),'David');
INSERT01
david=#select*fromtbl_xulie;
id|name ----+------- 2|Sandy 3|David (2rows)
效果同a.setval(regclass,bigint)
b.2setval(regclass,bigint,false)
david=#truncatetbl_xulie;
TRUNCATETABLE
david=#selectsetval('tbl_xulie_id_seq',1,false);
setval
-------- 1 (1row)
david=#insertintotbl_xulievalues(nextval('tbl_xulie_id_seq'),'Sandy');
INSERT01
david=#insertintotbl_xulievalues(nextval('tbl_xulie_id_seq'),'David');
INSERT01
david=#select*fromtbl_xulie;
id|name ----+------- 1|Sandy 2|David (2rows)
方法二：修改序列
修改序列的语法：
ALTERSEQUENCEname[INCREMENT[BY]increment]
[MINVALUEminvalue|NOMINVALUE][MAXVALUEmaxvalue|NOMAXVALUE]
[START[WITH]start]
[RESTART[[WITH]restart]]
[CACHEcache][[NO]CYCLE]
[OWNEDBY{table.column|NONE}]
ALTERSEQUENCEnameOWNERTOnew_owner
ALTERSEQUENCEnameRENAMETOnew_name
ALTERSEQUENCEnameSETSCHEMAnew_schema
实例：
david=#truncatetbl_xulie;
TRUNCATETABLE
david=#altersequencetbl_xulie_id_seqrestartwith0;
ERROR:RESTARTvalue(0)cannotbelessthanMINVALUE(1)
david=#altersequencetbl_xulie_id_seqrestartwith1;
ALTERSEQUENCE
david=#insertintotbl_xulievalues(nextval('tbl_xulie_id_seq'),'David');
INSERT01
david=#insertintotbl_xulievalues(nextval('tbl_xulie_id_seq'),'Sandy');
INSERT01
david=#select*fromtbl_xulie;
id|name ----+------- 1|David 2|Sandy (2rows)
david=#selectnextval('tbl_xulie_id_seq');
nextval
--------- 3 (1row)
六、删除序列
语法：
DROPSEQUENCE[IFEXISTS]name[,...][CASCADE|RESTRICT]
当有表字段使用到PG序列时，不能直接删除。
david=#dropsequencetbl_xulie2_id_seq; ERROR:cannotdropsequencetbl_xulie2_id_seqbecauseotherobjectsdependonit DETAIL:defaultfortabletbl_xulie2columniddependsonsequencetbl_xulie2_id_seq HINT:UseDROP...CASCADEtodropthedependentobjectstoo. david=#droptabletbl_xulie2; DROPTABLE david=#dropsequencetbl_xulie2_id_seq; DROPSEQUENCE david=#
说明：对于序列是由建表时指定serial创建的，删除该表的同时，对应的序列也会被删除。
七、其他说明
a.currval取得的是当前会话的序列值，在当前会话中该值不会因为其他会话取了nextval而变化。会变化的是全局的last_value值,并且当前会话中如果没有读过nextval值时直接读currval是会报错的。
b.对于序列是由建表时指定serial时创建时，删除该表的同时，对应的序列也会被删除。
c.表主键数据可以用跟表相关的序列，也可以用其他序列，但不推荐，只是PG默认它没错。
d.为使多用户并发下同一个序列取值不会重复，nextval是不会rollback的，不过可以使用setval重置
如果一个序列对象是带着缺省参数创建的，那么对它调用nextval将返回从1开始的后续的数值。其它的行为可以通过使用CREATESEQUENCE命令里的特殊参数获取；参阅其命令参考页获取更多信息。
e.为了避免从同一个序列获取数值的当前事务被阻塞，nextval操作决不会回滚；也就是说，一旦一个数值已经被抓走，那么就认为它已经用过了，即使调用nextval的事务后面又退出了也一样。这就意味着退出的事务可能在序列赋予的数值中留下"空洞"。setval操作也决不回滚。