数码控科技猎奇Iphone动漫星座游戏电竞lolcosplay王者荣耀攻略allcnewsBLOGNEWSBLOGASKBLOGBLOGZSK全部技术问答问答技术问答it问答代码软件新闻开发博客电脑/网络手机/数码笔记本电脑互联网操作系统软件硬件编程开发360产品资源分享电脑知识文档中心IT全部全部分类 全部分类技术牛文全部分类教程最新 网页制作cms教程平面设计媒体动画操作系统网站运营网络安全服务器教程数据库工具网络安全软件教学vbscript正则表达式javascript批处理更多»编程更新教程更新游戏更新allitnewsJava 新闻网络医疗信息化安全创业站长电商科技访谈域名会议专栏创业动态融资创投创业学院 / 产品经理创业公司人物访谈营销 开发数据库服务器系统虚拟化云计算 嵌入式移动开发作业作业1常见软件all电脑网络手机数码生活游戏体育运动明星影音休闲爱好文化艺术社会民生教育科学医疗健康金融管理情感社交地区其他电脑互联网软件硬件编程开发360相关产品手机平板其他电子产品摄影器材360硬件通讯智能设备购物时尚生活常识美容塑身服装服饰出行旅游交通汽车购房置业家居装修美食烹饪单机电脑游戏网页游戏电视游戏桌游棋牌游戏手机游戏小游戏掌机游戏客户端游戏集体游戏其他游戏体育赛事篮球足球其他运动球类运动赛车健身运动运动用品影视娱乐人物音乐动漫摄影摄像收藏宠物幽默搞笑起名花鸟鱼虫茶艺彩票星座占卜书画美术舞蹈小说图书器乐声乐小品相声戏剧戏曲手工艺品历史话题时事政治就业职场军事国防节日风俗法律法规宗教礼仪礼节自然灾害360维权社会人物升学入学人文社科外语资格考试公务员留学出国家庭教育学习方法语文物理生物工程学农业数学化学健康知识心理健康孕育早教内科外科妇产科儿科皮肤科五官科男科整形中医药品传染科其他疾病医院两性肿瘤科创业投资企业管理财务税务银行股票金融理财基金债券保险贸易商务文书国民经济爱情婚姻家庭烦恼北京上海重庆天津黑龙江吉林辽宁河北内蒙古山西陕西宁夏甘肃青海新疆西藏四川贵州云南河南湖北湖南山东江苏浙江安徽江西福建广东广西海南香港澳门台湾海外地区

深入解读PostgreSQL中的序列及其相关函数的用法

日期:2016/5/5 17:20:00 来源:本网整理
s">

深入解读PostgreSQL中的序列及其相关函数的用法

作者:David Camp 字体:[增加 减小] 类型:转载 时间:2016-01-05 我要评论

这篇文章主要介绍了PostgreSQL中的序列及其相关函数的用法,包括序列的更新和删除等重要知识,需要的朋友可以参考下 dy("art_up") ">

一、简介

序列对象(也叫序列生成器)就是用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=# 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 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 数据迁移后更新序列

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)

五、序列函数

下面序列函数,为我们从序列对象中获取最新的序列值提供了简单和并发读取安全的方法。

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 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)

方法二:修改序列

修改序列的语法:

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)

六、删除序列

语法:


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 创建的,删除该表的同时,对应的序列也会被删除。

七、其他说明
a.currval取得的是当前会话的序列值,在当前会话中该值不会因为其他会话取了nextval而变化。会变化的是全局的last_value值,并且当前会话中如果没有读过nextval值时直接读currval是会报错的。
b.对于序列是由建表时指定serial时创建时,删除该表的同时,对应的序列也会被删除。
c.表主键数据可以用跟表相关的序列,也可以用其他序列,但不推荐,只是PG默认它没错。
d.为使多用户并发下同一个序列取值不会重复,nextval是不会rollback的,不过可以使用setval重置
如果一个序列对象是带着缺省参数创建的,那么对它调用 nextval 将返回从1 开始的后续的数值。 其它的行为可以通过使用 CREATE SEQUENCE 命令里的 特殊参数获取;参阅其命令参考页获取更多信息。
e.为了避免从同一个序列获取数值的当前事务被阻塞, nextval 操作决不会回滚;也就是说,一旦一个数值已经被抓走, 那么就认为它已经用过了,即使调用 nextval 的事务后面又退出了也一样。这就意味着退出的事务可能在序列赋予的数值中留下"空洞"。 setval 操作也决不回滚。

  • 本文相关:
  • Postgresql ALTER语句常用操作小结
  • PostgreSQL教程(一):数据表详解
  • PostgreSQL教程(二):模式Schema详解
  • PostgreSQL教程(三):表的继承和分区表详解
  • PostgreSQL教程(四):数据类型详解
  • PostgreSQL教程(五):函数和操作符详解(1)
  • PostgreSQL教程(六):函数和操作符详解(2)
  • PostgreSQL教程(七):函数和操作符详解(3)
  • PostgreSQL教程(八):索引详解
  • PostgreSQL教程(九):事物隔离介绍
  • 免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved