暂无图片
暂无图片
1
暂无图片
暂无图片
暂无图片
首页 / PostgreSQL 序列

PostgreSQL 序列

原创 贾勇智 2022-06-22
1547

[[toc]]

适用范围

PostgreSQL 12.8+

序列概述

  • 序列是bigint 整数，取值范围：-263~263-1
  • 序列属于relation其中之一，所以因同一schema下，不能与ordinary table, index, TOAST table,view,materialized view, composite type, foreign table, partitioned table, partitioned index同名。
  • 序列创建后，可以使用函数currval()，nextval(),setval(),lastval()对其进行操作。
函数 返回值 说明
currval() bigint nextval返回当前会话中此序列最近获得的值。（如果nextval在此会话中从未为此序列调用过，则会报告错误。）因为这将返回一个会话本地值，所以它给出了一个可预测的答案，无论nextval自当前会话以来其他会话是否已执行。此功能需要USAGE或SELECT对序列具有特权。
nextval() bigint 将序列对象前进到下一个值并返回该值。这是原子完成的：即使多个会话nextval同时执行，每个会话都将安全地接收不同的序列值。如果使用默认参数创建了序列对象，则连续nextval调用将返回从 1 开始的连续值。此功能需要USAGE或UPDATE对序列具有特权。
setval() bigint 设置序列对象的当前值，以及可选的is_called标志。双参数形式将序列的last_value字段设置为指定值并将其is_called字段设置为true，这意味着下一个nextval将在返回值之前推进序列。将报告的currval值也设置为指定值。在三参数形式中，is_called可以设置为true或false。true与双参数形式的效果相同。如果设置为false，则 nextval将准确返回指定的值，并且序列推进从以下 开始nextval。此功能需要UPDATE序列的特权。
lastval() bigint nextval返回当前会话中最近返回的值。此函数与 相同currval，不同之处在于它不是将序列名称作为参数，而是引用nextval当前会话中最近应用的序列。lastval如果nextval当前会话中尚未调用，则调用是错误的。此功能需要USAGE或SELECT对最后使用的序列具有特权。

序列操作

  • 创建一个索引
postgres@[local]:1931=#26535 create schema seq;
CREATE SCHEMA
postgres@[local]:1931=#26535 create sequence seq.seq1 minvalue 1 maxvalue 100 start 1;
CREATE SEQUENCE
postgres@[local]:1931=#26535 select * from seq.seq1
postgres-# ;
 last_value | log_cnt | is_called 
------------+---------+-----------
          1 |       0 | f
(1 row)
  • 表中使用序列
create table seq.product(id int default nextval('seq.seq1'),
name varchar);
postgres@[local]:1931=#26535 insert into seq.product values(10,'tom');
INSERT 0 1
postgres@[local]:1931=#26535 insert into seq.product (name)values('tom');
INSERT 0 1
postgres@[local]:1931=#26535 insert into seq.product (name)values('tom');
INSERT 0 1
postgres@[local]:1931=#26535 select * from seq.product;
 id | name 
----+------
 10 | tom
  1 | tom
  2 | tom
(3 rows)
  • 修改序列属性
#修改前
postgres@[local]:1931=#26535 select * from pg_sequences where sequencename='seq1';
 schemaname | sequencename | sequenceowner | data_type | start_value | min_value | max_value | increment_by | cycle | cache_size | last_value 
------------+--------------+---------------+-----------+-------------+-----------+-----------+--------------+-------+------------+------------
 seq        | seq1         | postgres      | bigint    |           1 |         1 |       100 |            1 | f     |          1 |          2
(1 row)
#修改后

postgres@[local]:1931=#26535 alter sequence seq.seq1  maxvalue 1000 start with 20 cache 5 restart 5;
ALTER SEQUENCE
postgres@[local]:1931=#26535 select * from pg_sequences where sequencename='seq1';
 schemaname | sequencename | sequenceowner | data_type | start_value | min_value | max_value | increment_by | cycle | cache_size | last_value 
------------+--------------+---------------+-----------+-------------+-----------+-----------+--------------+-------+------------+------------
 seq        | seq1         | postgres      | bigint    |          20 |         1 |      1000 |            1 | f     |          5 |           
(1 row)
  • 查看序列 select * from seq_name 或 select currval()
postgres@[local]:1931=#26535 create sequence seq.seq2 start with 1 maxvalue 9999 increment by 1;
CREATE SEQUENCE
postgres@[local]:1931=#26535 select * from seq.seq2;
 last_value | log_cnt | is_called 
------------+---------+-----------
          1 |       0 | f
(1 row)

#这里要非常注意currval()函数，只有在当前会话，调用过nextval()后，才会返回最近的值，不然会报错。
postgres@[local]:1931=#26535 select currval('seq.seq2');
ERROR:  currval of sequence "seq2" is not yet defined in this session
postgres@[local]:1931=#26535 select nextval('seq.seq2');
 nextval 
---------
       1
(1 row)

postgres@[local]:1931=#26535 select currval('seq.seq2');
 currval 
---------
       1
(1 row)
  • 查看最近的值 lastval() 与currval()相似，必须先运行nextval()
#如不先运行nextval()，则报以下错。
[postgres@pg14 ~]$ psql
psql (14.2)
Type "help" for help.

postgres@[local]:1931=#26774 select lastval();
ERROR:  lastval is not yet defined in this session
postgres@[local]:1931=#26774 

postgres@[local]:1931=#26774 select nextval('seq.seq2');
 nextval 
---------
       3
(1 row)

postgres@[local]:1931=#26774 select lastval();
 lastval 
---------
       3
(1 row)
  • reset序列 setval()
# 不写boolean 与 boolean=true  等同，运行nextval()时，加setval + 1
postgres@[local]:1931=#26774 select setval('seq.seq2',1);
 setval 
--------
      1
(1 row)

postgres@[local]:1931=#26774 select nextval('seq.seq2');
 nextval 
---------
       2
(1 row)

postgres@[local]:1931=#26774 select setval('seq.seq2',1,true);
 setval 
--------
      1
(1 row)

postgres@[local]:1931=#26774 select nextval('seq.seq2');
 nextval 
---------
       2
(1 row)

# 当boolean = false 运行nextval()时，返回setval值
postgres@[local]:1931=#26774 select setval('seq.seq2',1,false);
 setval 
--------
      1
(1 row)

postgres@[local]:1931=#26774 select nextval('seq.seq2');
 nextval 
---------
       1
(1 row)
  • 删除序列
postgres@[local]:1931=#26774 drop sequence seq.seq2;
DROP SEQUENCE
postgres@[local]:1931=#26774
  • 删除正在被表使用的序列会报错，只有将表删除后，才能删序列
postgres@[local]:1931=#26774 drop sequence seq.seq1;
ERROR:  cannot drop sequence seq.seq1 because other objects depend on it
DETAIL:  default value for column id of table seq.product depends on sequence seq.seq1
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

postgres@[local]:1931=#26774 drop table  seq.product;
DROP TABLE
postgres@[local]:1931=#26774 drop sequence seq.seq1;
DROP SEQUENCE
  • 那么列为自增类型时，是序列吗
postgres@[local]:1931=#26774 create table seq.tab_seq(id serial,name text);
CREATE TABLE
postgres@[local]:1931=#26774 \d seq.tab_seq
                                Table "seq.tab_seq"
 Column |  Type   | Collation | Nullable |                 Default                 
--------+---------+-----------+----------+-----------------------------------------
 id     | integer |           | not null | nextval('seq.tab_seq_id_seq'::regclass)
 name   | text    |           |          | 

postgres@[local]:1931=#26774 select * from pg_sequences where sequencename='tab_seq_id_seq';
 schemaname |  sequencename  | sequenceowner | data_type | start_value | min_value | max_value  | increment_by | cycle | cache_size | last_value 
------------+----------------+---------------+-----------+-------------+-----------+------------+--------------+-------+------------+------------
 seq        | tab_seq_id_seq | postgres      | integer   |           1 |         1 | 2147483647 |            1 | f     |          1 |           
(1 row)
# 可以看自动创建一个seq.tab_seq_id_seq序列，取值范围为1~2^31-1

# 如果删除自增列，tab_seq_id_seq会怎么样呢？
postgres@[local]:1931=#26774 alter table  seq.tab_seq drop column id;
ALTER TABLE
postgres@[local]:1931=#26774 \d seq.tab_seq 
              Table "seq.tab_seq"
 Column | Type | Collation | Nullable | Default 
--------+------+-----------+----------+---------
 name   | text |           |          | 

postgres@[local]:1931=#26774 select * from pg_sequences where sequencename='tab_seq_id_seq';
 schemaname | sequencename | sequenceowner | data_type | start_value | min_value | max_value | increment_by | cycle | cache_size | last_value 
------------+--------------+---------------+-----------+-------------+-----------+-----------+--------------+-------+------------+------------
(0 rows)

# 可以删除列同时，序列自动删除，这是与手动创建序列不同点

# 同样猜测删除表同时，序列也会自动删除
postgres@[local]:1931=#26774  alter table  seq.tab_seq  add column id serial;
ALTER TABLE
postgres@[local]:1931=#26774 select * from pg_sequences where sequencename='tab_seq_id_seq';
 schemaname |  sequencename  | sequenceowner | data_type | start_value | min_value | max_value  | increment_by | cycle | cache_size | last_value 
------------+----------------+---------------+-----------+-------------+-----------+------------+--------------+-------+------------+------------
 seq        | tab_seq_id_seq | postgres      | integer   |           1 |         1 | 2147483647 |            1 | f     |          1 |           
(1 row)

postgres@[local]:1931=#26774 drop table seq.tab_seq;
DROP TABLE
postgres@[local]:1931=#26774 select * from pg_sequences where sequencename='tab_seq_id_seq';
 schemaname | sequencename | sequenceowner | data_type | start_value | min_value | max_value | increment_by | cycle | cache_size | last_value 
------------+--------------+---------------+-----------+-------------+-----------+-----------+--------------+-------+------------+------------
(0 rows)

参考文档

https://www.postgresql.org/docs/current/functions-sequence.html
https://www.postgresql.org/docs/current/sql-altertable.html
https://www.postgresql.org/docs/current/sql-createsequence.html
https://www.postgresql.org/search/?q=alter+sequence

墨力计划postgresql
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论