postgresql-实现自增列的启用和禁用，启用后从最大值开始递增

已于 2022-07-05 10:26:39 修改
阅读量1.1k 收藏 1
点赞数 2
分类专栏： # postgresql日常学习 文章标签： postgresql dba 数据库开发 python
于 2021-05-27 10:25:11 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_45111959/article/details/117322248
版权

一、介绍

1、功能介绍：

POSTGRES实现自增列的启用和禁用，启用后自增列从最大值开始递增。

2、函数介绍：

br_on_off_default_value_seq (schema_name in varchar,table_name in varchar,column_name in varchar,on_off_flag in int)
（1）schema_name 模式名
（2）table_name 表名
（3）column_name 列名
（4）on_off_flag 0为禁用，1为启用

二、测试验证

1、创建测试环境：

drop schema br CASCADE;

drop table test_tab;

drop sequence test_seq;

create table test_tab(a int,b varchar);

create sequence test_seq start with 2 increment by 1 minvalue 2  maxvalue 9223372036854775807  no cycle cache 2;

alter table test_tab alter column a set default nextval('test_seq');

insert into test_tab(b) values('sun');

insert into test_tab(b) values('czg');

select * from test_tab;

在这里插入图片描述

2、禁用自增列：

select br_on_off_default_value_seq('public','test_tab','a',0);

3、插入测试数据：

insert into test_tab values(10,'lxg');

insert into test_tab values(12,'haha');

select * from test_tab;

在这里插入图片描述

4、启用自增列：

select br_on_off_default_value_seq('public','test_tab','a',1);

5、测试函数效果

insert into test_tab(b) values('br');

insert into test_tab(b) values('test');

select * from test_tab;

在这里插入图片描述

三、br_on_off_default_value_seq 函数定义

CREATE OR REPLACE FUNCTION br_on_off_default_value_seq (schema_name in varchar,table_name in varchar,column_name in varchar,on_off_flag in int)
RETURNS TEXT
AS $$
temp_schema = 'br'
temp_table = 'br_source_data_tab'

txdb_select_sql = 'select max({column_name}) from {schema_name}.{table_name}'\
.format(schema_name=schema_name,column_name=column_name,table_name=table_name)

txdb_create_source_data_tab = 'create table {schema_name}.{table_name}(schema_name varchar(100),table_name varchar(100),column_name varchar(100),column_max_value varchar(100),default_seq varchar(100),seqstart bigint,seqincrement bigint,seqmax bigint,seqmin bigint,seqcache bigint,seqcycle varchar(10),\
primary key(schema_name,table_name,column_name,column_max_value,default_seq))'\
.format(schema_name=temp_schema,table_name=temp_table)

txdb_create_source_schema = 'create schema {schema_name}'\
.format(schema_name=temp_schema)

txdb_judge_tab_exists = 'select count(*) from pg_tables where tablename =\'{table_name}\' and schemaname=\'{schema_name}\''\
.format(schema_name=temp_schema,table_name=temp_table)

txdb_judge_schema_exists = 'select count(*) from pg_catalog.pg_namespace where nspname=\'{schema_name}\''\
.format(schema_name=temp_schema)

#判断temp_schema模式是否存在-1
sql_pre_judge_schema_exists = plpy.prepare(txdb_judge_schema_exists)
txdb_select_result_judge_schema_exists = plpy.execute(sql_pre_judge_schema_exists)
if txdb_select_result_judge_schema_exists[0]['count'] == 0: 
	sql_pre_txdb_create_source_schema = plpy.prepare(txdb_create_source_schema)
	plpy.execute(sql_pre_txdb_create_source_schema)
	
#判断temp_schema.temp_table表是否存在-2
sql_pre_judge_tab_exists = plpy.prepare(txdb_judge_tab_exists)
txdb_select_result_judge_tab_exists = plpy.execute(sql_pre_judge_tab_exists)
if txdb_select_result_judge_tab_exists[0]['count'] == 0: 
	sql_pre_txdb_create_source_data_tab = plpy.prepare(txdb_create_source_data_tab)
	plpy.execute(sql_pre_txdb_create_source_data_tab)

if on_off_flag == 1:
	#查询传入参数表上包含的默认值序列
	txdb_select_seq_sql = 'SELECT column_default FROM information_schema.columns WHERE (table_schema, table_name) = (\'{schema_name}\', \'{table_name}\') and column_name =\'{column_name}\''\
	.format(schema_name=schema_name,table_name=table_name,column_name=column_name)
	sql_pre_select_seq_sql = plpy.prepare(txdb_select_seq_sql)
	txdb_select_result_select_seq_sql = plpy.execute(sql_pre_select_seq_sql)
	if txdb_select_result_select_seq_sql[0]['column_default'] is not None:
		return '%s模式的%s表上的默认值序列%s为启用状态，不能再启用'%(schema_name,table_name,txdb_select_result_select_seq_sql[0]['column_default'].split("'")[1])
		#序列名为：txdb_select_result_select_seq_sql[0]['column_default'].split("'")[1]	
											   		
	#把表中的最大值更新到临时表中
	sql_pre = plpy.prepare(txdb_select_sql)
	txdb_select_result = plpy.execute(sql_pre)
	txdb_update_source_data_tab = 'update {schema_name}.{table_name} set column_max_value=$1 where schema_name=\'{schema_name_1}\' and table_name=\'{table_name_1}\' and column_name=\'{column_name}\' '\
	.format(schema_name=temp_schema,\
			table_name=temp_table,\
			schema_name_1=schema_name,\
			table_name_1=table_name,\
			column_name=column_name)
	try:
		sql_update_source_data_tab = plpy.prepare(txdb_update_source_data_tab,["text"])
		plpy.execute(sql_update_source_data_tab,[txdb_select_result[0]['max']])
	except BaseException as e:
		return '启用默认值序列阶段，更新临时表数据报错:%s'%(e)												   

	#从临时表中查出序列相关参数
	txdb_select_source_tab = 'select * from {schema_name}.{table_name} where schema_name=\'{schema_name_1}\' and table_name=\'{table_name_1}\' and column_name=\'{column_name}\''\
	.format(schema_name=temp_schema,\
			table_name=temp_table,\
			schema_name_1=schema_name,\
			table_name_1=table_name,\
			column_name=column_name)
	sql_pre_select_source_tab = plpy.prepare(txdb_select_source_tab)
	txdb_select_result_select_source_tab = plpy.execute(sql_pre_select_source_tab)
											   
	#生成序列
	if len(txdb_select_result_select_source_tab) == 0:
		return '请先传入参数0进行默认值序列禁用，再传入参数1进行默认值序列启用'
	elif txdb_select_result_select_source_tab[0]['seqcycle'] == 'True':
		txdb_sequence_cycle_value = 'cycle'																   
	elif txdb_select_result_select_source_tab[0]['seqcycle'] == 'False':
		txdb_sequence_cycle_value = 'no cycle'
	else:
		return '生成序列逻辑判断阶段出现未预见的情况'
	txdb_create_sequence = 'create sequence {schema_name}.{seq_name} start with {start_value} increment by {increment_value} minvalue {min_value}  maxvalue {max_value}  {cycle_value} cache {cache_value}'\
	.format(schema_name=txdb_select_result_select_source_tab[0]['schema_name'],\
			seq_name=txdb_select_result_select_source_tab[0]['default_seq'],\
			start_value=int(txdb_select_result_select_source_tab[0]['column_max_value'])+1,\
			increment_value=txdb_select_result_select_source_tab[0]['seqincrement'],\
			min_value=txdb_select_result_select_source_tab[0]['seqmin'],\
			max_value=txdb_select_result_select_source_tab[0]['seqmax'],\
			cycle_value=txdb_sequence_cycle_value,\
			cache_value=txdb_select_result_select_source_tab[0]['seqcache'])
	sql_pre_create_sequence = plpy.prepare(txdb_create_sequence)
	plpy.execute(sql_pre_create_sequence)	
											   
	#添加序列默认值
	txdb_add_default_value = 'alter table {table_schema}.{table_name} alter column {column_name} set default nextval(\'{seq_schema}.{seq_name}\')'\
	.format(table_schema=txdb_select_result_select_source_tab[0]['schema_name'],\
		   table_name=txdb_select_result_select_source_tab[0]['table_name'],\
		   column_name=txdb_select_result_select_source_tab[0]['column_name'],\
		   seq_schema=txdb_select_result_select_source_tab[0]['schema_name'],\
		   seq_name=txdb_select_result_select_source_tab[0]['default_seq'])
	#return txdb_add_default_value
	sql_pre_add_default_value = plpy.prepare(txdb_add_default_value)
	plpy.execute(sql_pre_add_default_value)	
	
elif on_off_flag == 0:
	#查询传入参数表上包含的默认值序列
	txdb_select_seq_sql = 'SELECT column_default FROM information_schema.columns WHERE (table_schema, table_name) = (\'{schema_name}\', \'{table_name}\') and column_name =\'{column_name}\''\
	.format(schema_name=schema_name,table_name=table_name,column_name=column_name)
	sql_pre_select_seq_sql = plpy.prepare(txdb_select_seq_sql)
	txdb_select_result_select_seq_sql = plpy.execute(sql_pre_select_seq_sql)
	if txdb_select_result_select_seq_sql[0]['column_default'] == None:
		return '%s模式的%s表上没有默认值序列,无法禁用'%(schema_name,table_name)
	#序列名为：txdb_select_result_select_seq_sql[0]['column_default'].split("'")[1]

	#查询序列定义
	txdb_select_seq_define_1 = 'select oid from pg_class where relname=\'{seq_name}\''.format(seq_name=txdb_select_result_select_seq_sql[0]['column_default'].split("'")[1])
	sql_pre_select_seq_define_1 = plpy.prepare(txdb_select_seq_define_1)
	txdb_select_result_select_seq_define_1 = plpy.execute(sql_pre_select_seq_define_1)	
	#序列名oid为:txdb_select_result_select_seq_define_1[0]['oid']
	
	txdb_select_seq_define_2 = 'select seqstart,seqincrement,seqmax,seqmin,seqcache,seqcycle from pg_sequence where seqrelid={oid_value}'.format(oid_value=txdb_select_result_select_seq_define_1[0]['oid'])
	sql_pre_select_seq_define_2 = plpy.prepare(txdb_select_seq_define_2)
	txdb_select_result_select_seq_define_2 = plpy.execute(sql_pre_select_seq_define_2)	
	#序列的定义：txdb_select_result_select_seq_define_2[0]
	
	#判断临时表中有没有传入参数表的数据，如果有进行更新,没有就插入-3
	txdb_judge_source_data_repeat = 'select count(*) from {schema_name}.{table_name} where table_name=\'{table_name_1}\' and column_name=\'{column_name}\''\
	.format(schema_name=temp_schema,table_name=temp_table,table_name_1=table_name,column_name=column_name)
	sql_pre_judge_source_data_repeat = plpy.prepare(txdb_judge_source_data_repeat)
	txdb_select_result_judge_source_data_repeat = plpy.execute(sql_pre_judge_source_data_repeat)
	sql_pre = plpy.prepare(txdb_select_sql)
	txdb_select_result = plpy.execute(sql_pre)
	if txdb_select_result_judge_source_data_repeat[0]['count'] == 0:
		#表中最大值插入到临时表中
		txdb_insert_source_data_tab = 'insert into  {schema_name}.{table_name} values ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)'\
		.format(schema_name=temp_schema,table_name=temp_table)
		try:
			sql_pre_insert_source_data_tab = plpy.prepare(txdb_insert_source_data_tab,["text","text","text","text","text","bigint","bigint","bigint","bigint","bigint","varchar"])
			plpy.execute(sql_pre_insert_source_data_tab,\
						 [schema_name,\
						 table_name,\
						 column_name,\
						  txdb_select_result[0]['max'],\
						  txdb_select_result_select_seq_sql[0]['column_default'].split("'")[1],\
						  txdb_select_result_select_seq_define_2[0]['seqstart'],\
						  txdb_select_result_select_seq_define_2[0]['seqincrement'],\
						  txdb_select_result_select_seq_define_2[0]['seqmax'],\
						  txdb_select_result_select_seq_define_2[0]['seqmin'],\
						  txdb_select_result_select_seq_define_2[0]['seqcache'],\
						  txdb_select_result_select_seq_define_2[0]['seqcycle']])
		except BaseException as e:
			return '数据无重复逻辑阶段报错:%s'%(e)
	elif txdb_select_result_judge_source_data_repeat[0]['count'] == 1:
		txdb_update_source_data_tab = 'update {schema_name}.{table_name} set column_max_value=$1 where schema_name=\'{schema_name_1}\' and table_name=\'{table_name_1}\' and column_name=\'{column_name}\' '\
		.format(schema_name=temp_schema,\
				table_name=temp_table,\
				schema_name_1=schema_name,\
				table_name_1=table_name,\
				column_name=column_name)
		try:
			sql_update_source_data_tab = plpy.prepare(txdb_update_source_data_tab,["text"])
			plpy.execute(sql_update_source_data_tab,[txdb_select_result[0]['max']])
		except BaseException as e:
			return '数据重复逻辑阶段报错:%s'%(e)	
																					   
	#删除默认值
	txdb_drop_default_seq = 'alter table {schema_name}.{table_name} alter column {column_name} drop default'\
	.format(schema_name=schema_name,table_name=table_name,column_name=column_name)		
	sql_drop_default_seq = plpy.prepare(txdb_drop_default_seq)
	plpy.execute(sql_drop_default_seq)
	
	#删除序列
	txdb_drop_seq = 'drop sequence {seq_name}'\
	.format(seq_name=txdb_select_result_select_seq_sql[0]['column_default'].split("'")[1])		
	sql_drop_seq = plpy.prepare(txdb_drop_seq)
	plpy.execute(sql_drop_seq)
	
else:
	return 'on_off_flag参数，开启为：1，关闭为：0，请不要输入其他值'
return 1

end
$$ LANGUAGE plpython3u;

如果有什么需要改进的地方，欢迎大家一起讨论。

阳光九叶草LXGZXJ
关注
  • 2
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
专栏目录
PostgreSql 设置自增字段
脑子进水养啥鱼？的博客
12-12 628
序列类型是 PostgreSQL 特有的创建一个自增列的方法。包含 smallserial、serial和 bigserial 类型，它们不是真正的类型，只是为了创建唯一标识符列而存在的方便符号。
Postgresql 创建自增序列并修改字段为自增
潘广宇的博客
04-08 934
CREATE SEQUENCE stat_pay_lv_series INCREMENT 1 START 1 NO MINVALUE NO MAXVALUE ALTER table stat_pay_lv alter column "id" set default nextval('stat_pay_lv_series');
如何在PostgreSQL中设置表的自动增长字段（序列），以及如何对其进行控制和管理
最新发布
程序员墨松
04-23 926
在PostgreSQL中，我们通常使用序列（SEQUENCE）来实现自动增长字段的功能。序列是一个数据库对象，它可以生成一个唯一的数字序列。这些数字可以被用作表中的主键或唯一标识符，从而实现自动增长的效果。记住，这些操作都需要相应的权限才能执行，所以在实际使用中，你需要确保你有足够的权限。字段就是自动增长的字段，它关联到了默认的序列（PostgreSQL会自动为这个字段创建一个序列）。的序列，它从1开始，每次增加1，没有设置最小值和最大值，并且缓存了一个值。要将序列关联到表的字段，你需要在创建表时，使用。
postgresql数据库如何实现主键自增
lqq1667821818的博客
06-10 3517
postgresql数据库如何实现主键自增 postgresql数据库可以创建主键，但是没有像mysql那样直接指定主键自增的auto_increment关键字，因此如果在postgresql中创建表指定主键自增使用auto_increment会报错。那么如何实现主键自增，postgresql通过序列来实现。序列可以实现自动增长，而且表字段可以指定默认值，因此可以通过指定默认值的方式来实现主键自增。 首先介绍一下序列 CREATE SEQUENCE创建一个新的序列数 发生器。这涉及到用名称name创建并且初
postgresql给已有的字段创建添加自增，并且自增值从最大的id+1开始
你太过耀眼的博客
12-26 3306
/*修改表的id属性为自增*/ CREATE SEQUENCE test_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; start with 序列开始值 increment by 递增值 给表添加自增属性 alter table test alter col...
postgrel中怎么使用sql函数可以实现所有数据的行号递增
weixin_35749440的博客
02-01 511
在PostgreSQL中，可以使用row_number()函数实现数据的行号递增。语法如下： select row_number() over (order by [排序列]), [其他列名] from [表名]; 其中，row_number() over (order by [排序列]) 表示在以[排序列]为标准进行排序的情况下，给数据按顺序递增排列。 [其他列名] 表示该表的其他数据列名。 ...
PostgreSQL SERIAL创建自增列
热门推荐
neweastsun的专栏
06-22 3万+
PostgreSQL SERIAL创建自增列 本文我们介绍PostgreSQL SERIAL，并展示如何使用serial类型创建表自增列。 PostgreSQL SERIAL伪类型 PostgreSQL序列是一种特殊的用于生产整数序列数据库对象。序列通常用于主键列，与mysql的AUTO_INCREMENT 概念类似。创建表时使用serial伪类型定义序列： CREATE TABLE table_...
ruoyi-postgresql设置自增序列.sql
09-10
ruoyi框架集成postgresql,对自增表设置序列
bitnami-docker-postgresql-repmgr：具有Replication Manager Docker映像的Bitnami PostgreSQL
02-15
$ docker run --name postgresql-repmgr bitnami/postgresql-repmgr:latest Docker撰写 $ curl -sSL ...
postgresql-42.5.0.jar
06-06
postgresql-42.5.0.jar在许多Java框架如Hibernate和Spring等中使用，并很受开发人员的欢迎。它是经过定期更新和改进的，以确保其与最新版本的PostgreSQL数据库兼容，同时提供了丰富的API和文档，方便程序员进行开发...
PostgreSQL（postgresql-13.5-1-windows-x64.exe）
11-20
PostgreSQL（postgresql-13.5-1-windows-x64.exe）适用于Windows x86-64 PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统（ORDBMS），是以加州大学计算机系开发的POSTGRES，4.2版本为基础的...
PostgreSQL（postgresql-13.5.tar.bz2）
11-20
PostgreSQL（postgresql-13.5.tar.bz2） PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统（ORDBMS），是以加州大学计算机系开发的POSTGRES，4.2版本为基础的对象关系型数据库管理系统。...
PostgreSQL设置主键自增、重置主键自增从1开始
qq_44862029的博客
06-21 1万+
如果表已经建好，则用序列设置如下图所示则成功 PostgreSQL设置主键自增 重置主键自增，从1开始 PostgreSQL重置主键自增从1开始 清空表数据，自增从1开始 PostgreSQL重置主键自增从1开始 参考：https://blog.csdn.net/weixin_43583693/article/details/105974638......
Postgresql实现主键自增
qq_43604221的博客
11-07 2826
Postgresql实现主键自增
PgSQL——学习笔记18： AUTO INCREMENT（自动增长）& PRIVILEGES（权限）
ZYF的博客
05-20 1197
目录 AUTO INCREMENT（自动增长）：会在新记录插入表中时生成一个唯一的数字。 PRIVILEGES（权限） GRANT命令：向用户分配权限 REVOKE 命令：取消权限 AUTO INCREMENT（自动增长）：会在新记录插入表中时生成一个唯一的数字。 PostgreSQL 使用序列来标识字段的自增长，数据类型有 smallserial、serial 和 bigserial 。 这些属性类似于 MySQL 数据库支持的 AUTO_INCREMENT 属性。 使用 MySQ..
PostgreSQL中三种自增列sequence，serial，identity区别
yuhui666666的博客
05-18 1379
总括：使用serial有时不会自增，引起id冲突 显示插入---插入的时候指定id 隐式插入---插入的时候不指定id PostgreSQL中三种自增列sequence，serial，identity区别 这三个对象都可以实现自增，这里从如下几个维度来看看这几个对象有哪些不同，其中功能性上看，大部分特性都是一致的或者类似的。 1、sequence在所有数据库中的性质都一样，它是跟具体的字段不是强绑定的，其特点是支持多个对个对象之间共享。 　sequenc...
【postgresql】postgresql数据库操作增列与id自增设置随手记
qq_41604569的博客
05-08 4032
postgresql数据库操作。
postgresql 触发器如何生成递增序列号，从1开始，并且每天重置
清晨qc的博客
11-02 1404
参数说明：IF EXISTS：如果该触发器不存在name：要移除的刚刚定义的触发器的名称。table_name：定义了该触发器的表的名称CASCADE：自动删除依赖于该触发器的对象，然后删除所有 依赖于那些对象的对象RESTRICT：如果有任何对象依赖于该触发器，则拒绝删除它。这是默认值。
Navicat创建pgsql序列自增
大白的博客
08-15 7988
每日学习一点点： 在使用pgsql的时候，如果需要对某一列设置默认值，会发现pgsql并不会像mysql那样可以很简单的通过设计表来对该字段进行设置。那么pgsql要怎么设置自增呢？ 方法一： 1：创建序列 所有者：表的库名 递增、当前值、开始值、最大小值 缓存：指定数据库预先分配序列的值供快速访问，意思就是可以提前提供一些自增至，比如：我现在想在id字段下面设置自增，把缓存设置为20，在我下次新增数据的时候，id会自增到2，但是这个2其实已经生成过了，不会现在生成，...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值