分享到 - [一键分享](#) - [QQ空间](#) - [新浪微博](#) - [百度搜藏](#) - [人人网](#) - [腾讯微博](#) - [百度相册](#) - [开心网](#) - [腾讯朋友](#) - [百度贴吧](#) - [豆瓣网](#) - [搜狐微博](#) - [百度新首页](#) - [QQ好友](#) - [和讯微博](#) - [更多...](#) [百度分享](#) 随笔-102  文章-0  评论-83  # [PostgreSQL 序列（SEQUENCE）](http://www.cnblogs.com/mchina/archive/2013/04/10/3012493.html) **一、简介** 序列对象（也叫序列生成器）就是用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 TABLE david=# ~~~ []( "复制代码") **方法二：**先创建序列名称，然后在新建的表中列属性指定序列就可以了，该列需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 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=# ~~~ []( "复制代码") []( "复制代码") ~~~ 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) david=# ~~~ []( "复制代码") **四、序列应用** 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) david=# ~~~ []( "复制代码") 4.2 数据迁移后更新序列 []( "复制代码") ~~~ 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) david=# ~~~ []( "复制代码") **五、序列函数** 下面序列函数，为我们从序列对象中获取最新的序列值提供了简单和并发读取安全的方法。 | **函数** | **返回类型** | **描述** | |-----|-----|-----| | nextval(regclass) | bigint | 递增序列对象到它的下一个数值并且返回该值。这个动作是自动完成的。即使多个会话并发运行nextval，每个进程也会安全地收到一个唯一的序列值。 | | currval(regclass) | bigint | 在当前会话中返回最近一次nextval抓到的该序列的数值。(如果在本会话中从未在该序列上调用过 nextval，那么会报告一个错误。)请注意因为此函数返回一个会话范围的数值，而且也能给出一个可预计的结果，因此可以用于判断其它会话是否执行过nextval。 | | lastval() | bigint | 返回当前会话里最近一次nextval返回的数值。这个函数等效于currval，只是它不用序列名为参数，它抓取当前会话里面最近一次nextval使用的序列。如果当前会话还没有调用过nextval，那么调用lastval将会报错。 | | setval(regclass, bigint) | bigint | 重置序列对象的计数器数值。设置序列的last_value字段为指定数值并且将其is_called字段设置为true，表示下一次nextval将在返回数值之前递增该序列。 | | setval(regclass, bigint, boolean) | bigint | 重置序列对象的计数器数值。功能等同于上面的setval函数，只是is_called可以设置为true或false。如果将其设置为false，那么下一次nextval将返回该数值，随后的nextval才开始递增该序列。 |   5.1 查看下一个序列值 []( "复制代码") ~~~ david=# select nextval('tbl_xulie_id_seq'); nextval --------- 3 (1 row) david=# select nextval('tbl_xulie_id_seq'); nextval --------- 4 (1 row) david=# ~~~ []( "复制代码") 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) david=# ~~~ []( "复制代码") 5.3 重置序列 **方法一：**使用序列函数 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) david=# ~~~ []( "复制代码") 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) david=# ~~~ []( "复制代码") 效果同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) david=# ~~~ []( "复制代码") **方法二：**修改序列 修改序列的语法： []( "复制代码") ~~~ 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 ~~~ []( "复制代码") 实例： []( "复制代码") ~~~ 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) david=# ~~~ []( "复制代码") **六、删除序列** 语法： ~~~ 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 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=# ~~~ []( "复制代码") **说明：**对于序列是由建表时指定serial 创建的，删除该表的同时，对应的序列也会被删除。 **七、参考资料** - PostgreSQL官方说明：[http://www.postgresql.org/docs/9.2/static/functions-sequence.html](http://www.postgresql.org/docs/9.2/static/functions-sequence.html) - PostgreSQL: 数据迁移之序列问题：[http://francs3.blog.163.com/blog/static/40576727201281351925766/](http://francs3.blog.163.com/blog/static/40576727201281351925766/) 分类: [Postgresql](http://www.cnblogs.com/mchina/category/381458.html) 标签: [postgresql](http://www.cnblogs.com/mchina/tag/postgresql/), [序列](http://www.cnblogs.com/mchina/tag/序列/), [SEQUENCE](http://www.cnblogs.com/mchina/tag/SEQUENCE/) 绿色通道： [好文要顶]()[关注我]()[收藏该文]()[与我联系](http://space.cnblogs.com/msg/send/David_Tang)[]( "分享至新浪微博") [](http://home.cnblogs.com/u/mchina/) [David_Tang](http://home.cnblogs.com/u/mchina/) [关注 - 1](http://home.cnblogs.com/u/mchina/followees) [粉丝 - 116](http://home.cnblogs.com/u/mchina/followers) [+加关注]() 0 0 (请您对文章做出评价) [« ](http://www.cnblogs.com/mchina/archive/2013/04/09/2973427.html) 上一篇：[PostgreSQL分区表（Table Partitioning）应用](http://www.cnblogs.com/mchina/archive/2013/04/09/2973427.html "发布于2013-04-09 11:14") [» ](http://www.cnblogs.com/mchina/archive/2013/04/15/3010418.html) 下一篇：[PostgreSQL的时间/日期函数使用](http://www.cnblogs.com/mchina/archive/2013/04/15/3010418.html "发布于2013-04-15 11:56") posted @ 2013-04-10 15:52[David_Tang](http://www.cnblogs.com/mchina/) 阅读(644) 评论(0) [编辑](http://www.cnblogs.com/mchina/admin/EditPosts.aspx?postid=3012493)[收藏](#)  Copyright ©2013 David_Tang