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

摘要：一、简介序列对象（也叫序列生成器）就是用CREATESEQUENCE创建的特殊的单行表。一个序列对象通常用于为行或者表生成唯一的标识符。二、...

一、简介

序列对象（也叫序列生成器）就是用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_id_seq

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;

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;

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)

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)

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数据库做地理位置app应用

★ PostgreSQL教程(四)：数据类型详解

★ PostgreSQL教程(十四)：数据库维护