序列的使用

最近更新时间:2024-09-27 15:59:51

我的收藏

序列的创建和访问

建立序列

示例:
[root@VM-0-14-tencentos ~]# psql -h 10.0.0.34 -p5432 -Udbadmin -dpostgres
Password for user dbadmin:
psql (16.0, server 10.0 @ TBase_v5.06.5.1 (commit: b0f2c3390))
Type "help" for help.
postgres=# create sequence tdsql_pg_seq;
CREATE SEQUENCE

建立序列,不存在时才创建

示例:
postgres=# create sequence IF NOT EXISTS tdsql_pg_seq;
NOTICE: relation "tdsql_pg_seq" already exists, skipping
CREATE SEQUENCE

查看序列当前的使用状况

示例:
postgres=# \\x
Expanded display is on.
postgres=# select * from tdsql_pg_seq ;
-[ RECORD 1 ]-
last_value | 1
log_cnt | 0
is_called | f

获取序列的下一个值

示例:
postgres=# select nextval('tdsql_pg_seq');
nextval
---------
1


获取序列的当前值,这个需要在访问 nextval() 后才能使用

示例:
postgres=# select currval('tdsql_pg_seq');
currval
---------
1
也可以用下面的方式来获取序列当前使用的值。
postgres=# select last_value from tdsql_pg_seq ;
last_value
------------
3

设置序列当前值

postgres=# select setval('tdsql_pg_seq',1);
setval
--------
1

序列在 DML 中使用

示例:
postgres=# INSERT INTO t (id,nickname) VALUES(nextval('tdsql_pg_seq'),'tdsql_pg好');
INSERT 0 1
postgres=# select * from t;
id | nickname
----+-----------
1 | 腾讯tdsql_pg
2 | tdsql_pg好
(2 rows)

序列作为字段的默认值使用

示例:
postgres=# alter table t alter column id set default nextval('tdsql_pg_seq');
postgres=# INSERT INTO t (nickname) VALUES('hello tdsql_pg');
INSERT 0 1
postgres=# select * from t;
id | nickname
----+-------------
3 | hello tdsql_pg
1 | 腾讯tdsql_pg
2 | tdsql_pg好
(3 rows)

序列作为字段类型使用

示例:
postgres=# drop table t;
DROP TABLE
postgres=# create table t (id serial not null,nickname text);
CREATE TABLE
postgres=# INSERT INTO t (nickname) VALUES('hello tdsql_pg');
INSERT 0 1
postgres=# select * from t;
id | nickname
----+-------------
1 | hello tdsql_pg
(1 row)

删除序列

示例:
postgres=# drop sequence tdsql_pg_seq;
DROP SEQUENCE

删除序列,不存在时跳过

示例:
postgres=# drop sequence IF EXISTS tdsql_pg_seq;
NOTICE: sequence "tdsql_pg_seq" does not exist, skipping
DROP SEQUENCE
postgres=#