• 售前

  • 售后

热门帖子
入门百科

postgresql 修改字段长度的操纵

[复制链接]
令狐佳人 显示全部楼层 发表于 2021-10-26 14:02:04 |阅读模式 打印 上一主题 下一主题
使用数据库postgresql的时间,有时会遇到字段长度扩展的情况,由于之前已经有数据了,以是只能修改字段长度,不能通过删除再增长的方式。
可以使用如下方式进行
  1. ALTER TABLE your_table_name alter COLUMN your_column_name type character varying(3000);
复制代码
通过上面的一句话就可以把你的表中相应的字段的长度修改为3000.
补充:PostgreSQL字符范例长度变动的性能
配景

业务有时会遇到表中的字符型字段的长度不够用的题目,必要修改表界说。但是表里的数据已经很多了,修改字段长度会不会造成应用堵塞呢?
测试验证

做了个小测试,如下
建表并插入1000w数据
  1. postgres=# create table tbx1(id int,c1 char(10),c2 varchar(10));
  2. CREATE TABLE
  3. postgres=# insert into tbx1 select id ,'aaaaa','aaaaa' from generate_series(1,10000000) id;
  4. INSERT 0 10000000
复制代码
变动varchar范例长度
  1. postgres=# alter table tbx1 alter COLUMN c2 type varchar(100);
  2. ALTER TABLE
  3. Time: 1.873 ms
  4. postgres=# alter table tbx1 alter COLUMN c2 type varchar(99);
  5. ALTER TABLE
  6. Time: 12815.678 ms
  7. postgres=# alter table tbx1 alter COLUMN c2 type varchar(4);
  8. ERROR: value too long for type character varying(4)
  9. Time: 5.328 ms
复制代码
变动char范例长度
  1. postgres=# alter table tbx1 alter COLUMN c1 type char(100);
  2. ALTER TABLE
  3. Time: 35429.282 ms
  4. postgres=# alter table tbx1 alter COLUMN c1 type char(6);
  5. ALTER TABLE
  6. Time: 20004.198 ms
  7. postgres=# alter table tbx1 alter COLUMN c1 type char(4);
  8. ERROR: value too long for type character(4)
  9. Time: 4.671 ms
复制代码
变动char范例,varchar和text范例互转
  1. alter table tbx1 alter COLUMN c1 type varchar(6);
  2. ALTER TABLE
  3. Time: 18880.369 ms
  4. postgres=# alter table tbx1 alter COLUMN c1 type text;
  5. ALTER TABLE
  6. Time: 12.691 ms
  7. postgres=# alter table tbx1 alter COLUMN c1 type varchar(20);
  8. ALTER TABLE
  9. Time: 32846.016 ms
  10. postgres=# alter table tbx1 alter COLUMN c1 type char(20);
  11. ALTER TABLE
  12. Time: 39796.784 ms
  13. postgres=# alter table tbx1 alter COLUMN c1 type text;
  14. ALTER TABLE
  15. Time: 32091.025 ms
  16. postgres=# alter table tbx1 alter COLUMN c1 type char(20);
  17. ALTER TABLE
  18. Time: 26031.344 ms
复制代码
界说变动后的数据

界说变动后,数据位置未变,即没有产生新的tuple
  1. postgres=# select ctid,id from tbx1 limit 5;
  2. ctid | id
  3. -------+----
  4. (0,1) | 1
  5. (0,2) | 2
  6. (0,3) | 3
  7. (0,4) | 4
  8. (0,5) | 5
  9. (5 rows)
复制代码
除varchar扩容以外的界说变动,每个tuple产生一条WAL纪录
  1. $ pg_xlogdump -f -s 3/BE002088 -n 5
  2. 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
  3. 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
  4. 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
  5. 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
  6. 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,全部关系数据库应该都实用。
以上为个人经验,盼望能给各人一个参考,也盼望各人多多支持草根技术分享。如有错误或未思量完全的地方,望不吝见教。

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作