postgresql 修改字段長度的操作
使用數據庫postgresql的時候,有時會遇到字段長度擴展的情況,由於之前已經有數據瞭,所以隻能修改字段長度,不能通過刪除再增加的方式。
可以使用如下方式進行
ALTER TABLE your_table_name alter COLUMN your_column_name type character varying(3000);
通過上面的一句話就可以把你的表中相應的字段的長度修改為3000.
補充:PostgreSQL字符類型長度變更的性能
背景
業務有時會遇到表中的字符型字段的長度不夠用的問題,需要修改表定義。但是表裡的數據已經很多瞭,修改字段長度會不會造成應用堵塞呢?
測試驗證
做瞭個小測試,如下
建表並插入1000w數據
postgres=# create table tbx1(id int,c1 char(10),c2 varchar(10)); CREATE TABLE postgres=# insert into tbx1 select id ,'aaaaa','aaaaa' from generate_series(1,10000000) id; INSERT 0 10000000
變更varchar類型長度
postgres=# alter table tbx1 alter COLUMN c2 type varchar(100); ALTER TABLE Time: 1.873 ms postgres=# alter table tbx1 alter COLUMN c2 type varchar(99); ALTER TABLE Time: 12815.678 ms postgres=# alter table tbx1 alter COLUMN c2 type varchar(4); ERROR: value too long for type character varying(4) Time: 5.328 ms
變更char類型長度
postgres=# alter table tbx1 alter COLUMN c1 type char(100); ALTER TABLE Time: 35429.282 ms postgres=# alter table tbx1 alter COLUMN c1 type char(6); ALTER TABLE Time: 20004.198 ms postgres=# alter table tbx1 alter COLUMN c1 type char(4); ERROR: value too long for type character(4) Time: 4.671 ms
變更char類型,varchar和text類型互轉
alter table tbx1 alter COLUMN c1 type varchar(6); ALTER TABLE Time: 18880.369 ms postgres=# alter table tbx1 alter COLUMN c1 type text; ALTER TABLE Time: 12.691 ms postgres=# alter table tbx1 alter COLUMN c1 type varchar(20); ALTER TABLE Time: 32846.016 ms postgres=# alter table tbx1 alter COLUMN c1 type char(20); ALTER TABLE Time: 39796.784 ms postgres=# alter table tbx1 alter COLUMN c1 type text; ALTER TABLE Time: 32091.025 ms postgres=# alter table tbx1 alter COLUMN c1 type char(20); ALTER TABLE Time: 26031.344 ms
定義變更後的數據
定義變更後,數據位置未變,即沒有產生新的tuple
postgres=# select ctid,id from tbx1 limit 5; ctid | id -------+---- (0,1) | 1 (0,2) | 2 (0,3) | 3 (0,4) | 4 (0,5) | 5 (5 rows)
除varchar擴容以外的定義變更,每個tuple產生一條WAL記錄
$ pg_xlogdump -f -s 3/BE002088 -n 5 rmgr: Heap len (rec/tot): 3/ 181, tx: 1733, lsn: 3/BE002088, prev 3/BE001FB8, desc: INSERT off 38, blkref #0: rel 1663/13269/16823 blk 58358 rmgr: Heap len (rec/tot): 3/ 181, tx: 1733, lsn: 3/BE002140, prev 3/BE002088, desc: INSERT off 39, blkref #0: rel 1663/13269/16823 blk 58358 rmgr: Heap len (rec/tot): 3/ 181, tx: 1733, lsn: 3/BE0021F8, prev 3/BE002140, desc: INSERT off 40, blkref #0: rel 1663/13269/16823 blk 58358 rmgr: Heap len (rec/tot): 3/ 181, tx: 1733, lsn: 3/BE0022B0, prev 3/BE0021F8, desc: INSERT off 41, blkref #0: rel 1663/13269/16823 blk 58358 rmgr: Heap len (rec/tot): 3/ 181, tx: 1733, lsn: 3/BE002368, prev 3/BE0022B0, desc: INSERT off 42, blkref #0: rel 1663/13269/16823 blk 58358
結論
varchar擴容,varchar轉text隻需修改元數據,毫秒完成。
其它轉換需要的時間和數據量有關,1000w數據10~40秒,但是不改變數據文件,隻是做檢查。
縮容時如果定義長度不夠容納現有數據報錯
不建議使用char類型,除瞭埋坑幾乎沒什麼用,這一條不僅適用與PG,所有關系數據庫應該都適用。
以上為個人經驗,希望能給大傢一個參考,也希望大傢多多支持WalkonNet。如有錯誤或未考慮完全的地方,望不吝賜教。
推薦閱讀:
- PostgreSQL12.5中分區表的一些操作實例
- PostgreSQL 如何修改文本類型字段的存儲方式
- PostgreSQL去掉表中所有不可見字符的操作
- 查看修改MySQL表結構命令
- 解決postgreSql 將Varchar類型字段修改為Int類型報錯的問題