今天又遇到一个需求，要把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
文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。
转载请注明本文地址：https://www.ucloud.cn/yun/130086.html
摘要：是一款开源的数据库，支持标准，用户可以通过驱动连接进行应用程序开发。本文就针对如何扩展功能，实现对接进行介绍。直接在中修改配置文件，只能在当前中生效，重新登录需要重新设置。 PostgreSQL是一款开源的SQL数据库，支持标准SQL，用户可以通过JDBC驱动连接PostgreSQL进行应用程序开发。用户通过扩展PostgreSQL功能，让开发者可以使用SQL语句访问SequoiaDB...
摘要：查询所有表名称以及字段含义表名名称字段字段备注列类型查看所有表名查看表名和备注查看特定表名备注查看特定表名字段 查询所有表名称以及字段含义 select c.relname 表名,cast(obj_description(relfilenode,pg_class) as varchar) 名称,a.attname 字段,d.description 字段备注,concat_ws(,t.t...
摘要：移动易后台实现外部数据库连接要实现外置数据库，即上层开发人员不关心下层数据库的实现，在项目中需要针对不同数据库修改文件以及在项目中添加依赖包。本文主要介绍移动易后台如何实现同不同数据源的连接，数据源包括，。 1、移动易后台实现外部数据库连接 要实现外置数据库，即上层开发人员不关心下层数据库的实现，在Spring boot项目 中需要针对不同数据库修改application.proper...
摘要：每个服务由多个进程组成，为首的进程名为。服务使用字节长的内部事务标识符，即时发生重叠后仍然继续使用，这会导致问题，所以需要定期进行操作。操作被认为是紧跟操作后的操作。在涉及高比例插入删除的表中，会造成索引膨胀，这时候可以重建索引。 简介和认知 发音 post-gres-q-l 服务（server） 一个操作系统中可以启动多个postgres服务。每个服务由多个进程组成，为首的进程名为p...
阅读 1343·2023-01-11 13:20
阅读 1679·2023-01-11 13:20
阅读 1130·2023-01-11 13:20
阅读 1852·2023-01-11 13:20
阅读 4095·2023-01-11 13:20
阅读 2703·2023-01-11 13:20
阅读 1383·2023-01-11 13:20
阅读 3590·2023-01-11 13:20
