PostgreSQL创建序列,查询全部序列,删除序列,生成序列创建sql语句
PostgreSQL创建序列,查询序列,删除序列,生成序列创建sql语句序列号(SERIAL)类型smallserial(int2),serial(int4)bigserial(int8)CREATE TABLE t_testsmaserial ("Id" serial,"Name" VARCHAR (200));序列函数函数返回类型描述currval(regclass)bigint返回最近一次用
·
PostgreSQL创建序列,查询全部序列,删除序列,生成序列创建sql语句
序列号(SERIAL)类型
smallserial(int2),
serial(int4)
bigserial(int8)
CREATE TABLE t_testsmaserial (
"Id" serial,
"Name" VARCHAR (200)
);
序列函数
函数 | 返回类型 | 描述 |
---|---|---|
currval(regclass) | bigint | 返回最近一次用 nextval 获取的指定序列的数值 |
nextval(regclass) | bigint | 递增序列并返回新值 |
setval(regclass, bigint) | bigint | 设置序列的当前数值 |
setval(regclass, bigint, boolean) | bigint | 设置序列的当前数值以及 is_called 标志 |
-- 设置序列的当前数值:
SELECT
setval(
'"aud_alter_id_seq"' :: regclass,
100
);
-- 获取当前序列值:
SELECT
currval(
'"aud_alter_id_seq"' :: regclass
);
-- 递增序列并返回新值:
SELECT
nextval(
'"aud_alter_id_seq"' :: regclass
);
生成序列创建sql语句
--查询全部序列
SELECT
*
FROM
pg_class
WHERE
relkind = 'S'
-- and relowner = (SELECT usesysid FROM pg_user WHERE usename = '用户名')
--生成已有序列创建语句
select
'CREATE SEQUENCE '|| relname ||' INCREMENT BY 1;'
FROM
pg_class
WHERE
relkind = 'S'
创建序列
CREATE SEQUENCE act_evt_log_log_nr__seq INCREMENT BY 1 ;
CREATE SEQUENCE aud_alter_id_seq INCREMENT BY 1 ;
CREATE SEQUENCE client_debug_log_id_seq INCREMENT BY 1 ;
CREATE SEQUENCE global_id_sequence INCREMENT BY 1 ;
CREATE SEQUENCE kx_cost_put_customer_id_seq INCREMENT BY 1 ;
CREATE SEQUENCE kx_cost_put_items_id_seq INCREMENT BY 1 ;
CREATE SEQUENCE t_certificate_c_certificateid_seq INCREMENT BY 1 ;
CREATE SEQUENCE kx_kpi_month_history_id_seq INCREMENT BY 1 ;
CREATE SEQUENCE kx_productid INCREMENT BY 1 ;
CREATE SEQUENCE price_id INCREMENT BY 1 ;
CREATE SEQUENCE kx_pinventoryid INCREMENT BY 1 ;
CREATE SEQUENCE kx_portrait_detail_seq INCREMENT BY 1 ;
CREATE SEQUENCE price_id_detail INCREMENT BY 1 ;
CREATE SEQUENCE price_manu INCREMENT BY 1 ;
CREATE SEQUENCE price_mid_id INCREMENT BY 1 ;
CREATE SEQUENCE sequence_portrait_detail INCREMENT BY 1 ;
CREATE SEQUENCE tbl_xulie2_id_seq INCREMENT BY 1 ;
CREATE SEQUENCE utl_seq_number_id_seq INCREMENT BY 1 ;
CREATE SEQUENCE bw_maindata_seq INCREMENT BY 1 ;
CREATE SEQUENCE file_task_id_seq INCREMENT BY 1 ;
DROP SEQUENCE seq_test;
CREATE SEQUENCE seq_test INCREMENT BY 2 MINVALUE 1 MAXVALUE 5 START WITH 2 NO CYCLE;
INCREMENT BY : 每次序列增加(或减少)的步长
MINVALUE : 序列最小值,NO MINVALUE表示没有最小值
MAXVALUE : 序列最大值,NO MAXVALUE表示没有最大值
START WITH :以什么序列值开始
CYCLE : 序列是否循环使用
OWNED BY : 可以直接指定一个表的字段,也可以不指定。
更多推荐
已为社区贡献15条内容
所有评论(0)