序列使用
更新时间 2025-02-14 10:23:35
最近更新时间: 2025-02-14 10:23:35
本页介绍天翼云TeleDB数据库的序列使用方法。
序列创建与访问
创建序列
teledb=# create sequence t_seq; CREATE SEQUENCE建立序列，不存在时才创建
teledb=# create sequence if not exists t_seq; NOTICE: relation "t_seq" already exists, skipping CREATE SEQUENCE查看序列当前的使用状况
teledb=# select * from t_seq; last_value | log_cnt | is_called ------------+---------+----------- 1 | 0 | f (1 row)获取序列的下一个值
teledb=# select nextval('t_seq'); nextval --------- 1 (1 row)获取序列的当前值，这个需要在访问nextval()后才能使用
teledb=# select currval('t_seq'); currval --------- 1 (1 row)设置序列当前值
teledb=# select setval('t_seq',2); setval -------- 2 (1 row)
序列在DML 中使用
teledb=# insert into t_update values(nextval('t_seq'),'teledb');
INSERT 0 1
teledb=# select * from t_update;
id | name | age
----+---------+-----
3 | teledb |
(1 row)序列作为字段的默认值使用
teledb=# alter table t_update alter column id set default nextval('t_seq');
ALTER TABLE
teledb=# insert into t_update(name) values('seqval');
INSERT 0 1
teledb=# select * from t_update;
id | name | age
----+---------+-----
3 | teledb |
4 | seqval |
(2 rows)序列作为字段类型使用
teledb=# create table t (id serial not null,nickname text);
CREATE TABLE
teledb=# insert into t(nickname) values('seq_val');
INSERT 0 1
teledb=# select * from t;
id | nickname
----+----------
1 | seq_val
(1 row)删除序列
存在依赖对象时，无法删除，可通过cascade级联删除
teledb=# drop sequence t_seq;
ERROR: cannot drop sequence t_seq because other objects depend on it
DETAIL: default for table t_update column id depends on sequence t_seq
HINT: Use DROP ... CASCADE to drop the dependent objects too.
teledb=# drop sequence t_seq cascade;
NOTICE: drop cascades to default for table t_update column id
DROP SEQUENCE删除序列，不存在时跳过
teledb=# drop sequence if exists t_seq;
NOTICE: sequence "t_seq" does not exist, skipping
DROP SEQUENCE
