修改表字段类型长度_PG修改字段

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

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

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

BEGIN; DROP VIEW view_name ALTER 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 rule DETAIL: 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 a Table "public.a" Column |         Type          | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- id    | integer               |           |          | name  | character varying(30) |           |          | postgres=# insert into a values(1,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaa'); INSERT 0 1 postgres=# select lengthb(name) from a; lengthb --------- 29 postgres=# select * from a_view; id |             name               ----+------------------------------- 1 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaa

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

情况二：修改字段类型

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

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

BEGIN; select deps_save_and_drop_dependencies('public', 'a'); alter table a alter name type varchar(30); select deps_restore_dependencies('public', 'a'); COMMIT

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

函数可以在github上下载：

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

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

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

参考文档：

Problemwith Postgres ALTER TABLE

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