PG修改字段

IT那活兒發(fā)布于2023-01-11 13:20 / 3675人閱讀

PG修改字段

今天又遇到一個(gè)需求，要把PG中的字段類(lèi)型修改一下。本來(lái)以為是個(gè)很簡(jiǎn)單的事情，畢竟Oracle就是一條指令就行了。但是在PG中改字段真的真的太難了。

當(dāng)你修改表字段的時(shí)候，會(huì)報(bào)ERROR: cannot alter type of a column used by a view or rule.

這主要是因?yàn)檫@個(gè)表上存在視圖或者是rule，rule這里代表是觸發(fā)器。所以在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;

這樣干也沒(méi)什么問(wèn)題，但是一旦上百個(gè)視圖依賴于一張表，或者視圖有多個(gè)嵌套，這問(wèn)題就麻煩起來(lái)了，特別是有的視圖定義動(dòng)輒上百上千行的，修改字段再創(chuàng)建視圖，一套弄下來(lái)就特別累。那么就沒(méi)有什么完美的解決辦法嗎？

通過(guò)研究，發(fā)現(xiàn)這個(gè)問(wèn)題有兩種解決辦法，針對(duì)兩種不同的情況。

情況一：只修改長(zhǎng)度

修改長(zhǎng)度，是在日常維護(hù)中經(jīng)常發(fā)生的。比如以前一個(gè)字段是20個(gè)長(zhǎng)度，運(yùn)行一段時(shí)間之后，發(fā)現(xiàn)長(zhǎng)度不夠要擴(kuò)成30。這個(gè)時(shí)候一般就會(huì)通知dba進(jìn)行操作。我們可以通過(guò)修改pg_attribute基表的方式來(lái)繞開(kāi)這個(gè)限制。

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

-----------

這里需要注意的一點(diǎn)是我設(shè)置的是varchar(20)，查出來(lái)的是varchar(24)，這是因?yàn)闅v史原因，添加了4。我如果要改成30，這里就需要修改為34。

改完之后我們?cè)賮?lái)查詢我們的表和視圖，發(fā)現(xiàn)都是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

雖然這樣修改能解決問(wèn)題，但是確實(shí)有一定發(fā)生錯(cuò)誤的風(fēng)險(xiǎn)，所以需要謹(jǐn)慎使用，最好要經(jīng)過(guò)詳細(xì)的評(píng)審和測(cè)試之后再操作。

情況二：修改字段類(lèi)型

修改字段類(lèi)型這種情況多見(jiàn)于執(zhí)行SQL緩慢，通過(guò)執(zhí)行計(jì)劃發(fā)現(xiàn)是字段類(lèi)型不匹配產(chǎn)生了隱式在轉(zhuǎn)換，而無(wú)法使用上索引。

這種情況就得通過(guò)我們之前的方法來(lái)實(shí)現(xiàn)，把刪除視圖、修改字段、創(chuàng)建視圖放到一個(gè)事務(wù)下執(zhí)行，但是如果嵌套的視圖比較多就很麻煩。為了克服這個(gè)麻煩，就有一個(gè)大神級(jí)人物寫(xiě)了兩個(gè)函數(shù)來(lái)輕松實(shí)現(xiàn)了這個(gè)問(wèn)題。由于太多人受到這個(gè)“煩惱”問(wèn)題的困擾，作者得到了極高的贊揚(yáng)。