序列创建与访问

• 创建序列

  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