更改varchar列的大小为较低的长度

我有一个关于ALTER TABLE命令在一个非常大的表(几乎3000万行)上的问题。 其中一列是varchar(255)，我想将其大小调整为varchar(40)。 基本上，我想通过运行以下命令来更改我的列:

ALTER TABLE mytable ALTER COLUMN mycolumn TYPE varchar(40);

我没有问题，如果进程很长，但似乎我的表在ALTER TABLE命令期间不再可读。 有没有更聪明的办法?也许添加一个新列，从旧列复制值，删除旧列，最后重命名新列?< / p >

我使用PostgreSQL 9.0。

362661 次浏览
小开
最佳答案

在在不改变数据的情况下调整PostgreSQL表中的列的大小中有关于如何做到这一点的描述。你得破解数据库目录数据。正式做到这一点的唯一方法是使用ALTER TABLE，正如您所注意到的，change将在运行时锁定并重写整个表。

在修改之前，请确保阅读了文档中的字符类型部分。这里有各种奇怪的案例。长度检查是在将值存储到行中时进行的。如果您在其中修改了一个下限，则根本不会减少现有值的大小。明智的做法是对整个表进行扫描，寻找字段长度为40个字符的行。你需要弄清楚如何手动截断这些锁——这样你就会得到一些超大的锁——因为如果有人试图更新该行上的任何内容，它会因为太大而拒绝它，此时它会存储该行的新版本。对用户来说，滑稽感随之而来。

VARCHAR是一个可怕的类型，它存在于PostgreSQL中只是为了遵守与之相关的SQL标准的可怕部分。如果您不关心多数据库兼容性，可以考虑将数据存储为TEXT，并添加一个约束来限制其长度。你可以在没有表锁/重写问题的情况下改变约束，它们可以做更多的完整性检查，而不仅仅是弱长度检查。

小开

下面是Greg Smith描述的页面的缓存。如果它也死了，alter语句看起来像这样:

UPDATE pg_attribute SET atttypmod = 35+4
WHERE attrelid = 'TABLE1'::regclass
AND attname = 'COL1';

您的表是TABLE1，列是COL1，您希望将其设置为35个字符(根据链接，+4是遗留目的所需要的，可能是注释中A.H.提到的开销)。

小开

我正面临着同样的问题，试图将VARCHAR从32截断为8，并获得ERROR: value too long for type character varying(8)。我希望尽可能地接近SQL，因为我使用的是自制的类似于jpa的结构，我们可能不得不根据客户的选择切换到不同的DBMS (PostgreSQL是默认的)。因此，我不想使用修改System表的技巧。

我结束了在ALTER TABLE中使用USING语句:

ALTER TABLE "MY_TABLE" ALTER COLUMN "MyColumn" TYPE varchar(8)
USING substr("MyColumn", 1, 8)

正如@raylu指出的那样，ALTER在表上获得了一个排他锁，因此所有其他操作将被延迟，直到它完成。

小开
我找到了一个非常简单的方法来改变大小，即注释@Size(min = 1, max = 50)，这是import javax.validation的一部分。约束”。 “进口javax.validation.constraints.Size;”< / p > 
@Size(min = 1, max = 50)
private String country;




when executing  this is hibernate you get in pgAdmin III




CREATE TABLE address
(
.....
country character varying(50),


.....


)
小开

好吧，我可能要迟到了，但是…

在您的情况下，不需要调整列的大小!

Postgres与其他一些数据库不同，它足够聪明，只使用足够的空间来适应字符串(甚至对较长的字符串使用压缩)，因此即使您的列声明为VARCHAR(255) -如果您在列中存储40个字符的字符串，空间使用将是40字节+ 1字节的开销。

短字符串(最大126字节)的存储空间要求为1字节 加上实际的字符串，其中包括大小写中的空格填充 的性格。较长的字符串有4个字节的开销，而不是1个。 长字符串由系统自动压缩，因此 对磁盘的物理要求可能更少。很长的值也是 存储在后台表中，这样它们就不会干扰快速 访问较短的列值

(http://www.postgresql.org/docs/9.0/interactive/datatype-character.html)

VARCHAR中的大小规范仅用于检查插入的值的大小，它不会影响磁盘布局。事实上，VARCHAR和TEXT字段在Postgres中以相同的方式存储。

小开

添加新列，用旧列替换新列，在红移postgresql上为我工作，更多详细信息请参阅此链接https://gist.github.com/mmasashi/7107430

BEGIN;
LOCK users;
ALTER TABLE users ADD COLUMN name_new varchar(512) DEFAULT NULL;
UPDATE users SET name_new = name;
ALTER TABLE users DROP name;
ALTER TABLE users RENAME name_new TO name;
END;
小开

在PostgreSQL 9.1中有一个更简单的方法

http://www.postgresql.org/message-id/162867790801110710g3c686010qcdd852e721e7a559@mail.gmail.com < a href = " http://www.postgresql.org/message-id/162867790801110710g3c686010qcdd852e721e7a559@mail.gmail.com " > < / >

CREATE TABLE foog(a varchar(10));


ALTER TABLE foog ALTER COLUMN a TYPE varchar(30);


postgres=# \d foog


Table "public.foog"
Column |         Type          | Modifiers
--------+-----------------------+-----------
a      | character varying(30) |
小开

如果你把alter放到一个事务中，表不应该被锁定:

BEGIN;
ALTER TABLE "public"."mytable" ALTER COLUMN "mycolumn" TYPE varchar(40);
COMMIT;

这对我来说非常有效，几秒钟就搞定了400,000多行。

小开

试着运行下面的alter表:

ALTER TABLE public.users
ALTER COLUMN "password" TYPE varchar(300)
USING "password"::varchar;

提交答案