PG修改字段

今天又遇到一个需求,要把PG中的字段类型修改一下。本来以为是个很简单的事情,毕竟Oracle就是一条指令就行了。但是在PG中改字段真的真的太难了。

PG修改字段

当你修改表字段的时候,会报ERROR: cannot alter type of a column used by a view or rule.

PG修改字段

这主要是因为这个表上存在视图或者是rule,rule这里代表是触发器。所以在PG中它不能像Oracle那样修改字段。一般做法就是:

BEGIN;DROP VIEW view_nameALTER TABLE users ALTER COLUMN column_name TYPE character varying(500);CREATE VIEW view_name AS SELECT * FROM table_name;COMMIT;

这样干也没什么问题,但是一旦上百个视图依赖于一张表,或者视图有多个嵌套,这问题就麻烦起来了,特别是有的视图定义动辄上百上千行的,修改字段再创建视图,一套弄下来就特别累。那么就没有什么完美的解决办法吗?

通过研究,发现这个问题有两种解决办法,针对两种不同的情况。

情况一:只修改长度

修改长度,是在日常维护中经常发生的。比如以前一个字段是20个长度,运行一段时间之后,发现长度不够要扩成30。这个时候一般就会通知dba进行操作。我们可以通过修改pg_attribute基表的方式来绕开这个限制。

create table a(id int ,name varchar(20));create view a_view as select id,name from a;
alter table a alter name type varchar(30);ERROR: cannot alter type of a column used by a view or ruleDETAIL: rule _RETURN on view a_view depends on column “name”
SELECT atttypmod FROM pg_attribute WHERE attrelid = ‘a’::regclass AND attname = ‘name’;atttypmod———–24(1 row)
update pg_attribute set atttypmod =34 WHERE attrelid =’a’::regclass AND attname = ‘name’;UPDATE 1
SELECT atttypmod FROM pg_attribute WHERE attrelid = ‘a’::regclass AND attname = ‘name’;atttypmod———–34

这里需要注意的一点是我设置的是varchar(20),查出来的是varchar(24),这是因为历史原因,添加了4。我如果要改成30,这里就需要修改为34。

改完之后我们再来查询我们的表和视图,发现都是ok的。

postgres=# \d aTable “public.a”Column |         Type          | Collation | Nullable | Default——–+———————–+———–+———-+———id    | integer               |           |          |name  | character varying(30) |           |          |
postgres=# insert into a values(1,’aaaaaaaaaaaaaaaaaaaaaaaaaaaaa’);INSERT 0 1postgres=# select lengthb(name) from a;lengthb———29
postgres=# select * from a_view;id |             name              —-+——————————-1 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaa

虽然这样修改能解决问题,但是确实有一定发生错误的风险,所以需要谨慎使用,最好要经过详细的评审和测试之后再操作。

情况二:修改字段类型

修改字段类型这种情况多见于执行SQL缓慢,通过执行计划发现是字段类型不匹配产生了隐式在转换,而无法使用上索引。

这种情况就得通过我们之前的方法来实现,把删除视图、修改字段、创建视图放到一个事务下执行,但是如果嵌套的视图比较多就很麻烦。为了克服这个麻烦,就有一个大神级人物写了两个函数来轻松实现了这个问题。由于太多人受到这个“烦恼”问题的困扰,作者得到了极高的赞扬。

PG修改字段
BEGIN;select deps_save_and_drop_dependencies(‘public’, ‘a’);alter table a alter name type varchar(30);select deps_restore_dependencies(‘public’, ‘a’);COMMIT

以下是我在自己环境中进行的测试,非常简单就搞定了。

PG修改字段

函数可以在github上下载:

https://gist.github.com/mateuszwenus/11187288(PG12之前版本)

https://gist.github.com/briandignan/03ef42e78434658cf27f052e2f0798e8(PG12之后的版本)

如果让我推荐,我还是推荐使用第二种方法,毕竟这个方法比较稳妥一点。也基本上达到了比较完美的地步。就算遇到上百个视图或者像俄罗斯套娃一样的视图你也不用担心了。

PG修改字段

参考文档:

Problemwith Postgres ALTER TABLE

https://stackoverflow.com/questions/3243863/problem-with-postgres-alter-table/49000321

来源:IT那活儿,本文观点不代表自营销立场,网址:https://www.zyxiao.com/p/100047

发表评论

电子邮件地址不会被公开。 必填项已用*标注

侵权联系
分享本页
返回顶部