在不锁定表的情况下，将PostgreSQL中的列类型从VARCHAR更改为TEXT。

Question

我有一个“父表”，按年份分区，并且还有很多列，现在我需要将一个VARCHAR(32)列更改为TEXT，因为我们需要更大的长度灵活性。

所以我将修改父表，并且也会更改所有分区。

但是该表有2个唯一索引和1个索引与此列相关。

这个查询会锁定表：

ALTER TABLE my_schema.my_table
ALTER COLUMN column_need_change TYPE VARCHAR(64) USING 
column_need_change :: VARCHAR(64);

也包括这个：

ALTER TABLE my_schema.my_table
ALTER COLUMN column_need_change TYPE TEXT USING column_need_change :: TEXT;

我看到这个解决方案：

UPDATE pg_attribute SET atttypmod = 64+4
WHERE attrelid = 'my_schema.my_table'::regclass
AND attname = 'column_need_change ';

但我不喜欢这个解决方案。

如何在不锁定表的情况下将VARCHAR(32)类型更改为TEXT，我需要在更新之间继续向表中推送一些数据。

我的Postgresql版本：9.6

编辑：

这是我最终采取的解决方案：

ALTER TABLE my_schema.my_table
ALTER COLUMN column_need_change TYPE TEXT USING column_need_change :: TEXT;

查询锁定了我的表，耗时1分52秒548毫秒，涉及260万行数据，但是没问题。

- MaximeF

Answer 1

支持且安全的方法是使用 ALTER TABLE。这不会重写表格，因为 varchar 和 text 在磁盘上具有相同的表示方式，所以一旦获得 ACCESS EXCLUSIVE 表锁定，它将在一瞬间完成。

只要您的事务很短，您只会经历短暂的停顿，而 ALTER TABLE 等待所有先前的事务完成。

篡改系统目录是危险的，你自己承担风险。

你可能会逃脱。

UPDATE pg_attribute
SET atttypmod = -1,
    atttypid  = 25
WHERE attrelid = 'my_schema.my_table'::regclass
  AND attname = 'column_need_change';

但是如果它（指某物）断了，你可以留下碎片......

- Laurenz Albe

---

@IMSoP：如果更改是“兼容”的，例如从varchar（20）到varchar（500），Oracle和SQL Server也不会重写表格（或任何索引）。 - user330315

@a_horse_with_no_name 哦，好的。我记得读过SQL Server根据指定的类型和精度为每行分配固定宽度，所以我认为如果分配了额外的空间，那么行就需要用新的布局重新编写。当然，在Postgres中，varchar(max)并不像text一样被鼓励作为默认值。 - IMSoP

@IMSoP：这里的“固定宽度”是用于估计查询结果所需的内存，而不是用于将数据存储在磁盘上。例如，请参见此处：https://dba.stackexchange.com/a/162117/1822 - user330315

感谢@LaurenzAlbe，我会使用ALTER TABLE解决方案，虽然它会锁定我的表两分钟，但没关系。 - MaximeF

Postgres文档表明，虽然ALTER TABLE版本不会为这些类型重写表（列内容没有更改且类型是二进制可强制转换的），但它将重写使用该列的任何索引，这将锁定表一段时间。 如果这是一个大表，则索引可能也很大，因此重写它们可能需要相当长的时间。 我遇到了这种情况。 给其他勇敢的读者留个注。 - sj26