PG修改字段

IT那活兒發布于2023-01-11 13:20 / 3654人閱讀

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

-----------

(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

-----------

這里需要注意的一點是我設置的是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

---------

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

云服務器 GPU云服務器修改表字段類型修改表字段名 oracle修改表字段 mysql修改數據庫字段長度

文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。

轉載請注明本文地址：http://specialneedsforspecialkids.com/yun/130086.html

PostgreSQL對接SequoiaDB

摘要：是一款開源的數據庫，支持標準，用戶可以通過驅動連接進行應用程序開發。本文就針對如何擴展功能，實現對接進行介紹。直接在中修改配置文件，只能在當前中生效，重新登錄需要重新設置。 PostgreSQL是一款開源的SQL數據庫，支持標準SQL，用戶可以通過JDBC驅動連接PostgreSQL進行應用程序開發。用戶通過擴展PostgreSQL功能，讓開發者可以使用SQL語句訪問SequoiaDB...

TZLLOG 2019-07-25 12:30 評論0 收藏0
PostgreSQL查詢表以及字段備注

摘要：查詢所有表名稱以及字段含義表名名稱字段字段備注列類型查看所有表名查看表名和備注查看特定表名備注查看特定表名字段查詢所有表名稱以及字段含義 select c.relname 表名,cast(obj_description(relfilenode,pg_class) as varchar) 名稱,a.attname 字段,d.description 字段備注,concat_ws(,t.t...

anonymoussf 2019-07-25 12:39 評論0 收藏0
移動易開源APP組合套件更新——支持多種外部數據庫，支持全文搜索

摘要：移動易后臺實現外部數據庫連接要實現外置數據庫，即上層開發人員不關心下層數據庫的實現，在項目中需要針對不同數據庫修改文件以及在項目中添加依賴包。本文主要介紹移動易后臺如何實現同不同數據源的連接，數據源包括，。 1、移動易后臺實現外部數據庫連接要實現外置數據庫，即上層開發人員不關心下層數據庫的實現，在Spring boot項目中需要針對不同數據庫修改application.proper...

anyway 2019-08-15 10:57 評論0 收藏0
PostgreSQL的實踐一：初識

摘要：每個服務由多個進程組成，為首的進程名為。服務使用字節長的內部事務標識符，即時發生重疊后仍然繼續使用，這會導致問題，所以需要定期進行操作。操作被認為是緊跟操作后的操作。在涉及高比例插入刪除的表中，會造成索引膨脹，這時候可以重建索引。簡介和認知發音 post-gres-q-l 服務（server）一個操作系統中可以啟動多個postgres服務。每個服務由多個進程組成，為首的進程名為p...

yibinnn 2019-07-25 12:38 評論0 收藏0