Postgresql - 将 varchar 列的大小更改为较低的长度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7729287/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql - change the size of a varchar column to lower length
提问by Labynocle
I have a question about the ALTER TABLEcommand on a really large table (almost 30 millions rows).
One of its columns is a varchar(255)and I would like to resize it to a varchar(40).
Basically, I would like to change my column by running the following command:
我ALTER TABLE对一个非常大的表（近 3000 万行）上的命令有疑问。其中一列是 a varchar(255)，我想将其大小调整为 a varchar(40)。基本上，我想通过运行以下命令来更改我的列：
ALTER TABLE mytable ALTER COLUMN mycolumn TYPE varchar(40);
I have no problem if the process is very long but it seems my table is no more readable during the ALTER TABLE command. Is there a smarter way? Maybe add a new column, copy values from the old column, drop the old column and finally rename the new one?
如果过程很长，我没有问题，但在 ALTER TABLE 命令期间，我的表似乎不再可读。有没有更聪明的方法？也许添加一个新列，从旧列复制值，删除旧列，最后重命名新列？
Any clue will be greatly appreciated! Thanks in advance,
任何线索将不胜感激！提前致谢，
Note: I use PostgreSQL 9.0.
注意：我使用 PostgreSQL 9.0。
采纳答案by Greg Smith
There's a description of how to do this at Resize a column in a PostgreSQL table without changing data. You have to hack the database catalog data. The only way to do this officially is with ALTER TABLE, and as you've noted that change will lock and rewrite the entire table while it's running.
在Resize a column in a PostgreSQL table without changes data 中有关于如何执行此操作的描述。您必须破解数据库目录数据。正式执行此操作的唯一方法是使用 ALTER TABLE，正如您所指出的，更改将在运行时锁定并重写整个表。
Make sure you read the Character Typessection of the docs before changing this. All sorts of weird cases to be aware of here. The length check is done when values are stored into the rows. If you hack a lower limit in there, that will not reduce the size of existing values at all. You would be wise to do a scan over the whole table looking for rows where the length of the field is >40 characters after making the change. You'll need to figure out how to truncate those manually--so you're back some locks just on oversize ones--because if someone tries to update anything on that row it's going to reject it as too big now, at the point it goes to store the new version of the row. Hilarity ensues for the user.
在更改之前，请务必阅读文档的字符类型部分。在这里要注意各种奇怪的情况。当值存储到行中时，长度检查完成。如果你在那里修改一个下限，那根本不会减少现有值的大小。在进行更改后，您最好扫描整个表以查找字段长度大于 40 个字符的行。你需要弄清楚如何手动截断那些——所以你只在超大的锁上放了一些锁——因为如果有人试图更新那一行上的任何东西，它现在会因为太大而拒绝它，在这一点上它去存储行的新版本。随之而来的是用户的欢笑。
VARCHAR is a terrible type that exists in PostgreSQL only to comply with its associated terrible part of the SQL standard. If you don't care about multi-database compatibility, consider storing your data as TEXT and add a constraint to limits its length. Constraints you can change around without this table lock/rewrite problem, and they can do more integrity checking than just the weak length check.
VARCHAR 是一种可怕的类型，它存在于 PostgreSQL 中，只是为了遵守 SQL 标准中与之相关的可怕部分。如果您不关心多数据库兼容性，请考虑将数据存储为 TEXT 并添加约束以限制其长度。您可以在没有此表锁定/重写问题的情况下更改约束，并且它们可以进行比弱长度检查更多的完整性检查。
回答by sir_leslie
In PostgreSQL 9.1 there is an easier way
在 PostgreSQL 9.1 中有一个更简单的方法
http://www.postgresql.org/message-id/[email protected]
http://www.postgresql.org/message-id/[email protected]
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) |
回答by Sergey
Ok, I'm probably late to the party, BUT...
好吧，我可能迟到了，但是……
THERE'S NO NEED TO RESIZE THE COLUMN IN YOUR CASE!
无需在您的情况下调整列大小！
Postgres, unlike some other databases, is smart enough to only use just enough space to fit the string (even using compression for longer strings), so even if your column is declared as VARCHAR(255) - if you store 40-character strings in the column, the space usage will be 40 bytes + 1 byte of overhead.
与其他一些数据库不同，Postgres 足够聪明，只使用足够的空间来容纳字符串（甚至对更长的字符串使用压缩），因此即使您的列被声明为 VARCHAR(255) - 如果您将 40 个字符的字符串存储在列，空间使用量将是 40 字节 + 1 字节的开销。
The storage requirement for a short string (up to 126 bytes) is 1 byte plus the actual string, which includes the space padding in the case of character. Longer strings have 4 bytes of overhead instead of 1. Long strings are compressed by the system automatically, so the physical requirement on disk might be less. Very long values are also stored in background tables so that they do not interfere with rapid access to shorter column values.
短字符串（最多 126 个字节）的存储要求是 1 个字节加上实际字符串，其中包括字符情况下的空格填充。较长的字符串有 4 个字节的开销，而不是 1 个。系统会自动压缩长字符串，因此对磁盘的物理要求可能会更少。很长的值也存储在后台表中，这样它们就不会干扰对较短列值的快速访问。
(http://www.postgresql.org/docs/9.0/interactive/datatype-character.html)
( http://www.postgresql.org/docs/9.0/interactive/datatype-character.html)
The size specification in VARCHAR is only used to check the size of the values which are inserted, it does not affect the disk layout. In fact, VARCHAR and TEXT fields are stored in the same way in Postgres.
VARCHAR 中的大小规范仅用于检查插入值的大小，它不影响磁盘布局。事实上，VARCHAR 和 TEXT 字段在 Postgres 中的存储方式相同。
回答by Matthieu
I was facing the same problem trying to truncate a VARCHAR from 32 to 8 and getting the ERROR: value too long for type character varying(8). I want to stay as close to SQL as possible because I'm using a self-made JPA-like structure that we might have to switch to different DBMS according to customer's choices (PostgreSQL being the default one). Hence, I don't want to use the trick of altering System tables.
我在尝试将 VARCHAR 从 32 截断到 8 并获取ERROR: value too long for type character varying(8). 我想尽可能地接近 SQL，因为我使用的是一种自制的类似 JPA 的结构，我们可能必须根据客户的选择切换到不同的 DBMS（PostgreSQL 是默认的）。因此，我不想使用更改系统表的技巧。
I ended using the USINGstatement in the ALTER TABLE:
我结束了使用以下USING语句ALTER TABLE：
ALTER TABLE "MY_TABLE" ALTER COLUMN "MyColumn" TYPE varchar(8)
USING substr("MyColumn", 1, 8)
As @raylu noted, ALTERacquires an exclusive lock on the table so all other operations will be delayed until it completes.
正如@raylu 指出的那样，ALTER获取表上的独占锁，因此所有其他操作将被延迟，直到它完成。
回答by Hymantrade
if you put the alter into a transaction the table should not be locked:
如果您将更改放入事务中，则不应锁定表：
BEGIN;
ALTER TABLE "public"."mytable" ALTER COLUMN "mycolumn" TYPE varchar(40);
COMMIT;
this worked for me blazing fast, few seconds on a table with more than 400k rows.
这对我来说很快，在超过 40 万行的桌子上几秒钟。
回答by spats
Adding new column and replacing new one with old worked for me, on redshift postgresql, refer this link for more details https://gist.github.com/mmasashi/7107430
添加新列并用旧列替换新列对我有用，在 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;
回答by Tom
Here's the cacheof the page described by Greg Smith. In case that dies as well, the alter statement looks like this:
这是Greg Smith 描述的页面缓存。如果它也死了，alter 语句看起来像这样：
UPDATE pg_attribute SET atttypmod = 35+4
WHERE attrelid = 'TABLE1'::regclass
AND attname = 'COL1';
Where your table is TABLE1, the column is COL1 and you want to set it to 35 characters (the +4 is needed for legacy purposes according to the link, possibly the overhead referred to by A.H. in the comments).
如果您的表是 TABLE1，则该列是 COL1，并且您希望将其设置为 35 个字符（根据链接，+4 需要用于遗留目的，可能是注释中 AH 引用的开销）。
回答by Tito
I have found a very easy way to change the size i.e. the annotation @Size(min = 1, max = 50) which is part of "import javax.validation.constraints" i.e. "import javax.validation.constraints.Size;"
我找到了一种非常简单的方法来更改大小，即注释 @Size(min = 1, max = 50)，它是“import javax.validation.constraints”的一部分，即“import javax.validation.constraints.Size;”
@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),
.....
)
回答by Никита Верёвкин
Try run following alter table:
尝试运行以下更改表：
ALTER TABLE public.users
ALTER COLUMN "password" TYPE varchar(300)
USING "password"::varchar;
