深入解读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 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
序列
postgresql 序列、postgresql 序列使用、postgresql 查询序列、postgresql 创建序列、postgresql 重置序列，以便于您获取更多的相关知识。

时间： 2024-08-31 09:35:58

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

PostgreSQL中调用存储过程并返回数据集实例_PostgreSQL

这里用一个实例来演示PostgreSQL存储过程如何返回数据集. 1.首先准备数据表 复制代码 代码如下: //member_category create table member_category(id serial, name text, discount_rate real, base_integral integer); alter table member_category add primary key(id); alter table member_category add ch

解读JavaScript中 For, While与递归的用法_基础知识

for循环: 复制代码 代码如下: for(i=start; i<end; i++) { } while循环: (注意, 若条件一直为真, 则会进入死循环, 浏览器就hang掉) 复制代码 代码如下: while (condition) {     //do something;     //change condition;} 递归: 使用for循环做substring 复制代码 代码如下: function substring(all, start, end) {    for(i=star

在PostgreSQL中实现按拼音、汉字、拼音首字母搜索的例子

在PostgreSQL中实现按拼音.汉字.拼音首字母搜索的例子 作者 digoal 日期 2016-11-09 标签 PostgreSQL , 拼音 , 中文分词 , tsvector , 拼音首字母 , hmm , 词库 背景 PostgreSQL有很多特性是可以提升开发效率,提高生产力的. 在前端页面中,搜索是一个非常常用的功能,例如淘宝首页的搜索. 为了提升用户体验,用户可以按拼音首字母进行搜索,按中文单词搜索,或者按拼音的全部进行搜索. 又比如家里的电视盒子,因为没有实体键盘,按拼音首字

曾国藩的&quot;顺势而为&quot;在PostgreSQL中的价值体现

后人对曾国藩的一些评论:"曾国藩顺势而为 成就一番事业,并一定程度上做了一些推动历史前进的事情". "顺势而为"这个词实在是用得太好了,我们身在数字时代,更加应该懂得顺势而为的道理,比如在金融行业中,不断变化的股价:在物联网的传感器中,不断上报的数字:由数据监测不断画出的曲线等等,这些无不体现着一个字:"势". 我们来看看使用PostgreSQL数据库如何抓住这个"势"呢? 首先贴一个最小二乘法least-squares-fi

根据前序和中序序列生成二叉树

一.前言: 我的一个同事拿来她老公的远程教育的考试题,叫大家帮着做,我写了这道,源码通过VC6编译链接,执行成功,呵呵:)题目就是给出了一个二叉树的前序序列(ABDCEGF)和中序序列(DBAEGCF),要求根据这两个输入完成一个二叉树,但没有指定用什么方式,所以我用了最笨拙的顺序存储二叉树,不过用了map来保存二叉树.题目还要求形成二叉树后可以遍历这个二叉树,由于三种遍历只是顺序不同,所以我只实现了后序遍历,何况输入就是前序和中序.:)本来应该用template做的,不过同事要求不要用复杂的C

详细解读AngularJS中的表单验证编程

  这篇文章主要介绍了详细解读AngularJS中的表单验证编程,AngularJS是一个非常热门的JavaScript库,需要的朋友可以参考下 需求 Name 必填 Username 非必填,最小长度3,最大长度8 Email 非必填,但必须是合法的email 验证未通过的表单不能提交 显示一个必填或者非法email格式的错误信息 如果正确提交就弹出一个祝贺信息 现在知道我们的目标了吧,让我们一起来构建这个东西吧. Angular 的表单属性 $valid, $invalid, $pristi

ibatis的insert中使用序列

  Oracle中创建序列 create sequence MYDB_SEQUENCEincrement by 1 start with 1 nomaxvalue minvalue 1;   xml写法 <insert id="insert" parameterClass="Student">  <selectKey resultClass="String" keyProperty="mid">   S

9 篇顶会论文解读推荐中的序列化建模：Session-based Neural Recommendation

前言 本文对 Recurrent Neural Network 在推荐领域的序列数据建模进行梳理,整理推荐领域和深度学习领域顶会 RecSys.ICLR 等中的 9 篇论文进行整理.图片和文字来源于原文,帮助读者理解,有争议的请联系我. Session-based neural recommendation 首先介绍下 session-based 的概念:session 是服务器端用来记录识别用户的一种机制.典型的场景比如购物车,服务端为特定的对象创建了特定的 Session,用于标识这个对象,

postgresql java问题-Postgresql java编程用jdbc连接，怎么样执行postgresql中 d 的查询指令

问题描述 Postgresql java编程用jdbc连接,怎么样执行postgresql中 d 的查询指令 请各位大神帮忙解决一下这个问题:"Postgresql java编程用jdbc连接,怎么样执行postgresql中 d 的查询指令"谢谢. 情况是这样: 我要把所有表的constraints的column找出来,但是我只能通过pg_indexes找到constraints的名字,只有通过d constraints_name 才能找到column,所有请教各位大神有没有解决方法