PostgreSQL序列相关
-- 2022年5月28日20:45:57
-- 查看数据库中有哪些序列
-- r =普通表, i =索引,S =序列,v =视图,m =物化视图, c =复合类型,t = TOAST表,f =外部表
select * from pg_class where relkind='S';
select * from pg_class where relname='pg_sequence'; -- 表
select * from pg_class where relname='pg_sequences'; -- 视图
select * from pg_sequence;
select * from pg_sequences;
-- 序列相关操作
select * from generate_series(1,20);
select a,md5(a::varchar) from generate_series(1,20) a;
select a,md5(a::varchar) from generate_series(1,20,2) a;
-- http://postgres.cn/docs/9.4/sql-createsequence.html
-- 命令包含的全部格式
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_name.column_name | NONE } ]
select * from pg_class where relname = 'seq_test2_id1';
select * from pg_sequence;
select * from pg_sequences;
-- http://postgres.cn/docs/9.4/functions-sequence.html
-- 9.16. 序列操作函数
-- 测试默认值
-- 默认:从1开始(序列开始值为1 start,最小值为1 minvalue),步长为1( increment ),最大值为 9223372036854775807 ( maxvalue 比bigint最大值小一点),缓存为 1 (cache),不循环 ( NO CYCLE)
-- 可选的子句MINVALUE minvalue 指定序列的最小值。如果没有声明这个子句或者声明了NO MINVALUE, 那么递增序列的缺省为 1 ,递减序列的缺省为-2^63-1。
-- 可选的子句MAXVALUE maxvalue 指定序列的最大值。如果没有声明这个子句或者声明了NO MAXVALUE, 那么递增序列的缺省为2^63-1,递减序列的缺省为 -1 。
-- 最简单的创建序列的语句
CREATE SEQUENCE seq_test2_id1;
select nextval('seq_test2_id1'),nextval('seq_test2_id1');
select * from pg_sequence WHERE seqrelid = (select oid from pg_class where relname = 'seq_test2_id1');
-- seqrelid seqtypid seqstart seqincrement seqmax seqmin seqcache seqcycle
-- 5793567 20 1 1 9223372036854775807 1 1 f
select * from pg_sequences where sequencename = 'seq_test2_id1';
DROP SEQUENCE IF EXISTS seq_test2_id1;
-- 按照默认值,相当于
CREATE SEQUENCE seq_test2_id1 INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE;
-- seqrelid seqtypid seqstart seqincrement seqmax seqmin seqcache seqcycle
-- 5793764 20 1 1 9223372036854775807 1 1 f