服务器之家：专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - PostgreSQL - 深入解读PostgreSQL中的序列及其相关函数的用法

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

2020-04-28 14:35David Camp PostgreSQL

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

一、简介

序列对象（也叫序列生成器）就是用CREATE SEQUENCE 创建的特殊的单行表。一个序列对象通常用于为行或者表生成唯一的标识符。

二、创建序列

方法一：直接在表中指定字段类型为serial 类型

?
1
2
3
4
5
6
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 类型

创建序列的语法：

?
1
2
3
4
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 } ]

实例：

?
1
2
3
4
5
6
7
8
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=#

三、查看序列

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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=#

查看序列属性

?
1
david=# \d tbl_xulie_id_seq
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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
?
1
david=# select * from tbl_xulie2_id_seq;
?
1
2
3
4
  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 命令中使用序列

?
1
2
3
4
5
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;
?
1
2
3
4
5
id | name
----+-------
 1 | David
 2 | Sandy
(2 rows)

4.2 数据迁移后更新序列

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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;

 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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)
?
1
2
3
4
5
6
7
8
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
?
1
2
3
---------
   25
(1 row)
?
1
2
david=# select nextval('tbl_xulie_id_seq');
 nextval
?
1
2
3
---------
   100
(1 row)
?
1
david=# select nextval('tbl_xulie_id_seq');
?
1
2
3
4
nextval
---------
   101
(1 row)
?
1
2
3
4
5
6
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
?
1
2
3
--------
   25
(1 row)
?
1
2
3
4
5
david=# end;
COMMIT
david=# insert into tbl_xulie values (nextval('tbl_xulie_id_seq'), 'Flash');
INSERT 0 1
david=# select * from tbl_xulie;

 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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)
?
1
2
david=# select nextval('tbl_xulie_id_seq');
 nextval
?
1
2
3
---------
   27
(1 row)

五、序列函数

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

5.1 查看下一个序列值

?
1
2
david=# select nextval('tbl_xulie_id_seq');
 nextval
?
1
2
3
---------
    3
(1 row)
?
1
2
david=# select nextval('tbl_xulie_id_seq');
 nextval
?
1
2
3
---------
    4
(1 row)

5.2 查看序列最近使用值

?
1
2
david=# select nextval('tbl_xulie_id_seq');
 nextval
?
1
2
3
---------
    4
(1 row)
?
1
2
david=# select currval('tbl_xulie_id_seq');
 currval
?
1
2
3
---------
    4
(1 row)
?
1
2
david=# select currval('tbl_xulie_id_seq');
 currval
?
1
2
3
---------
    4
(1 row)

5.3 重置序列

方法一：使用序列函数

?
1
2
3
4
5
6
a. setval(regclass, bigint)
 
david=# truncate tbl_xulie;
TRUNCATE TABLE
david=# select setval('tbl_xulie_id_seq', 1);
 setval
?
1
2
3
--------
   1
(1 row)
?
1
2
3
4
5
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;
?
1
2
3
4
5
id | name
----+-------
 2 | Sandy
 3 | David
(2 rows)
?
1
2
david=# select currval('tbl_xulie_id_seq');
 currval
?
1
2
3
---------
    3
(1 row)
?
1
david=# select nextval('tbl_xulie_id_seq');
?
1
2
3
4
nextval
---------
    4
(1 row)
?
1
2
3
4
5
6
7
8
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
2
3
--------
   1
(1 row)
?
1
2
3
4
5
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;
?
1
2
3
4
5
id | name
----+-------
 2 | Sandy
 3 | David
(2 rows)


效果同a. setval(regclass, bigint)

?
1
2
3
4
5
6
b.2 setval(regclass, bigint, false)
 
david=# truncate tbl_xulie;
TRUNCATE TABLE
david=# select setval('tbl_xulie_id_seq', 1, false);
 setval
?
1
2
3
--------
   1
(1 row)
?
1
2
3
4
5
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;
?
1
2
3
4
5
id | name
----+-------
 1 | Sandy
 2 | David
(2 rows)

方法二：修改序列

修改序列的语法：

?
1
2
3
4
5
6
7
8
9
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

实例：

?
1
2
3
4
5
6
7
8
9
10
11
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;

 

?
1
2
3
4
5
id | name
----+-------
 1 | David
 2 | Sandy
(2 rows)
?
1
2
david=# select nextval('tbl_xulie_id_seq');
 nextval
?
1
2
3
---------
    3
(1 row)

六、删除序列

语法：

?
1
DROP SEQUENCE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

当有表字段使用到PG序列时，不能直接删除。

?
1
2
3
4
5
6
7
8
9
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

    • 延伸 · 阅读

    精彩推荐
    • PostgreSQLPostgresql开启远程访问的步骤全纪录

      Postgresql开启远程访问的步骤全纪录

      postgre一般默认为本地连接，不支持远程访问，所以如果要开启远程访问，需要更改安装文件的配置。下面这篇文章主要给大家介绍了关于Postgresql开启远程...

      我勒个去6812020-04-30
    • PostgreSQLpostgresql 数据库中的数据转换

      postgresql 数据库中的数据转换

      postgres8.3以后，字段数据之间的默认转换取消了。如果需要进行数据变换的话，在postgresql数据库中，我们可以用"::"来进行字段数据的类型转换。...

      postgresql教程网12482021-10-08
    • PostgreSQLPostgresql查询效率计算初探

      Postgresql查询效率计算初探

      这篇文章主要给大家介绍了关于Postgresql查询效率计算的相关资料，文中通过示例代码介绍的非常详细，对大家学习或者使用Postgresql具有一定的参考学习价...

      轨迹4622020-05-03
    • PostgreSQLPostgreSQL标准建表语句分享

      PostgreSQL标准建表语句分享

      这篇文章主要介绍了PostgreSQL标准建表语句分享，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...

      码上得天下7962021-02-27
    • PostgreSQL深入理解PostgreSQL的MVCC并发处理方式

      深入理解PostgreSQL的MVCC并发处理方式

      这篇文章主要介绍了深入理解PostgreSQL的MVCC并发处理方式,文中同时介绍了MVCC的缺点,需要的朋友可以参考下 ...

      PostgreSQL教程网3622020-04-25
    • PostgreSQL分布式 PostgreSQL之Citus 架构

      分布式 PostgreSQL之Citus 架构

      节点 Citus 是一种 PostgreSQL 扩展，它允许数据库服务器(称为节点)在“无共享(shared nothing)”架构中相互协调。这些节点形成一个集群，允许 PostgreSQL 保存比单...

      未知802023-05-07
    • PostgreSQLRDS PostgreSQL一键大版本升级技术解密

      RDS PostgreSQL一键大版本升级技术解密

      一、PostgreSQL行业位置 （一）行业位置 在讨论PostgreSQL（下面简称为PG）在整个数据库行业的位置之前，我们先看一下阿里云数据库在全球的数据库行业里的...

      未知1192023-05-07
    • PostgreSQLpostgresql 中的to_char()常用操作

      postgresql 中的to_char()常用操作

      这篇文章主要介绍了postgresql 中的to_char()常用操作，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...

      J符离13432021-04-12
    最近更新
    编辑推荐
    阅读排行
    热门标签
    5432 　 postgresql命令 　 数据库教程 　 入门命令 　 死锁进程 　 PostgreSQL教程 　 表继承 　 事物隔离 　 性能提升技巧 　 PL/pgSQL 　 过程语言 　 partitioning 　 PostgreSQL安装教程 　 跨库访问 　 postgresql数据库 　 并发处理 　 MVCC 　 Citus 　 OID 　 XID 　 表空间大小 　 Postgre 　 phpPgAdmin 　 phpPgAdmin错误 　 phpPgAdmin问题 　 phpPgAdmin配置文件 　 app应用 　 PostgreSQL配置 　 Postgresql下载 　 PostgreSQL入门教程 　
    © 2019-2023 服务器之家(www.zzvips.com) 版权所有关于我们联系我们版权申明网站地图