Postgresql - 将 varchar 列的大小更改为更短的长度

Question

我对一个非常大的表（近 3000 万行）上的 ALTER TABLE 命令有疑问。 其中一列是 varchar(255)，我想将其大小调整为 varchar(40)。 基本上，我想通过运行以下命令来更改我的列：

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

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

注意：我使用 PostgreSQL 9.0。

Answer 1

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

http://www.postgresql.org/message-id/ [电子邮件受保护]

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) |

Answer 2

有关于如何执行此操作的说明 在不更改数据的情况下调整 PostgreSQL 表中列的大小。您必须破解数据库目录数据。正式执行此操作的唯一方法是使用 ALTER TABLE，正如您所注意到的，更改将在运行时锁定并重写整个表。

在更改之前，请务必阅读文档的字符类型部分这。这里有各种奇怪的情况需要注意。当值存储到行中时，将完成长度检查。如果您设置一个下限，则根本不会减少现有值的大小。明智的做法是扫描整个表，查找更改后字段长度大于 40 个字符的行。您需要弄清楚如何手动截断它们——这样您就可以在超大的锁上恢复一些锁——因为如果有人尝试更新该行上的任何内容，那么此时它会因为太大而被拒绝它用于存储该行的新版本。随之而来的是用户的欢闹。

VARCHAR 是一种糟糕的类型，它存在于 PostgreSQL 中只是为了遵守 SQL 标准中与其相关的糟糕部分。如果您不关心多数据库兼容性，请考虑将数据存储为 TEXT 并添加约束来限制其长度。您可以更改约束，而不会出现表锁定/重写问题，并且它们可以执行更多的完整性检查，而不仅仅是弱长度检查。

Answer 3

好吧，我可能迟到了，但是......

没有必要根据您的情况调整列的大小！

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 中以相同的方式存储< /a>.

Answer 4

我遇到了同样的问题，试图将 VARCHAR 从 32 截断为 8，并得到错误：值对于类型字符变化而言太长(8)。我希望尽可能接近 SQL，因为我使用的是一种自制的类似 JPA 的结构，我们可能必须根据客户的选择切换到不同的 DBMS（PostgreSQL 是默认的）。因此，我不想使用更改系统表的技巧。

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

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

正如 @raylu 指出的，ALTER 获取表上的独占锁，因此所有其他操作都会被延迟直到完成。

Answer 5

如果将更改放入事务中，则表不应该被锁定：

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

这对我来说非常快，在具有超过 400k 行的表上只需几秒钟。

Answer 6

添加新列并将新列替换为旧列对我有用，在 redshift 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;

Answer 7

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

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

如果你的表是TABLE1，列是COL1，你想将其设置为35个字符（根据链接，+4是出于遗留目的，可能是开销AH 在评论中提到）。

Answer 8

尝试运行以下更改表：

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

Answer 9

我找到了一种非常简单的方法来更改大小，即注释@Size(min = 1, max = 50)，它是import javax.validation.constraints.Size

@Size(min = 1, max = 50)
private String country;

在休眠状态下执行此命令时，您将进入 pgAdmin III

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

.....

)