16.GaussDB序列的管理

1.序列的管理主要包含以下内容

创建管理
(1)创建序列
(2)创建序列并设置最大值，最小值，步长
(3)递增序列并返回新值
(4)设置序列的当前数值
(5)修改序列的属主,同修改视图的属主。
(6)删除序列
(7)使用元命令查看序列
(8)使用序列名称查询序列
(9)修改某个字段的默认值为序列

2.序列的使用演示

--创建序列
sjzt=> create sequence seq01;
CREATE SEQUENCE
--创建序列并设置最大值，最小值，步长
sjzt=> create sequence seq02 increment by 1 minvalue 1 maxvalue 99999 cache 1 nocycle;
CREATE SEQUENCE
-- 递增序列并返回新值
sjzt=> select nextval('seq01');
 nextval 
---------
       1
(1 row)
sjzt=>  select seq01.nextval;
 nextval 
---------
       2
(1 row)
-- 最近一次nextval返回的值,currval 函数暂时不支持。
sjzt=> select currval('seq01');
ERROR:  currval function is not supported
CONTEXT:  referenced column: currval
sjzt=> select seq01.currval;
ERROR:  currval function is not supported
CONTEXT:  referenced column: currval
-- 最近一次nextval返回的值
sjzt=> select lastval();
ERROR:  lastval function is not supported
CONTEXT:  referenced column: lastval
  -- 设置序列的当前数值
sjzt=> select setval('seq01',1);
 setval 
--------
      1
(1 row)

sjzt=> alter sequence seq01 maxvalue 99999;
ALTER SEQUENCE

--修改序列的属主,同修改视图的属主。
--都是需要当前用户是目标属主的成员。然后在目标属主中修改当前序列的属主。
--sjzt是jack的成员;
gaussdb=# \du
     List of roles
 Role name |Attributes|Member of                       
-----------+-+--------
 jack      | | {sjzt}
sjzt=\c - jack
Password for user jack: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "sjzt" as user "jack".
sjzt=> alter sequence sjzt.seq01 owner to jack;
ALTER SEQUENCE
sjzt=> 
--删除序列
sjzt=> drop sequence seq01;
DROP SEQUENCE
sjzt=> drop sequence seq02 cascade;
DROP SEQUENCE

-- 使用元命令查看序列
sjzt=> create sequence seq01;
CREATE SEQUENCE
sjzt=>  \d seq01 
             Sequence "sjzt.seq01"
    Column     |  Type   |        Value        
---------------+---------+---------------------
 sequence_name | name    | seq01
 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
 uuid          | bigint  | 11000002

-- 查询序列
sjzt=>  select * from seq01;
 sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called |   uuid   
---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------+----------
 seq01         |         -1 |           1 |            1 | 9223372036854775807 |         1 |           1 |       0 | f         | f         | 11000002
(1 row)

 --序列应用：
sjzt=> create table t2(id serial,name varchar(20),tag int); 
NOTICE:  CREATE TABLE will create implicit sequence "t2_id_seq" for serial column "t2.id"
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'id' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
sjzt=> insert into t2 values(default,'Jerry');
INSERT 0 1
sjzt=> insert into t2 values(default,'Tom');
INSERT 0 1
sjzt=> select * from t2;
 id | name  | tag 
----+-------+-----
  1 | Jerry |    
  2 | Tom   |    
(2 rows)

--修改某个字段的默认值为序列
sjzt=> alter table t2 alter tag set default nextval('seq01');
ALTER TABLE
sjzt=> insert into t2 values(default,'Jack');
INSERT 0 1
sjzt=> insert into t2 values(default,'Jhon');
INSERT 0 1
sjzt=> select * from t2;
 id | name  | tag 
----+-------+-----
  3 | Jack  |   1
  1 | Jerry |    
  2 | Tom   |    
  4 | Jhon  |   2
(4 rows)
--使用序列名称查询序列
sjzt=> select * from seq01;
 sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called |   uuid   
---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------+----------
 seq01         |          2 |           1 |            1 | 9223372036854775807 |         1 |           1 |       0 | f         | t         | 11000002
(1 row)

3.总结

这里需要注意，GaussDB的序列，不支持如下三种：

select currval('seq01');

select seq01.currval;

select lastval();

取而代之的是使用：select * from seq01;   直接使用序列的名称查看序列的最小值，最大值，当前值等详细信息。这个与以往其他数据库的使用略有差异。