postgresql 在不丢失数据的情况下增加 postgres 中不同类型字符的大小
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5488428/
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
Increasing the size of character varying type in postgres without data loss
提问by Elitmiar
I need to increase the size of a character varying(60) field in a postgres database table without data loss.
我需要在不丢失数据的情况下增加 postgres 数据库表中字符变化（60）字段的大小。
I have this command
我有这个命令
alter table client_details alter column name set character varying(200);
will this command increase the the field size from 60 to 200 without data loss?
这个命令会在不丢失数据的情况下将字段大小从 60 增加到 200 吗？
采纳答案by Erkan Haspulat
Referring to thisdocumentation, there would be no data loss, alter columnonly casts old data to new data so a cast between character data should be fine. But I don't think your syntax is correct, see the documentation I mentioned earlier. I think you should be using this syntax :
参考此文档，不会丢失数据，alter column只会将旧数据转换为新数据，因此字符数据之间的转换应该没问题。但是我认为您的语法不正确，请参阅我之前提到的文档。我认为你应该使用这种语法：
ALTER [ COLUMN ] column TYPE type [ USING expression ]
ALTER [COLUMN] 列类型类型 [使用表达式]
And as a note, wouldn't it be easier to just create a table, populate it and test :)
请注意，创建一个表，填充它并进行测试不是更容易吗:)
回答by Peter prabu
The correct query to change the data type limit of the particular column:
更改特定列的数据类型限制的正确查询：
ALTER TABLE client_details ALTER COLUMN name TYPE character varying(200);
回答by Tometzky
Yes. But it will rewrite this table and lock it exclusively for duration of rewriting — any query trying to access this table will wait until rewrite finishes.
是的。但是它会重写这个表并在重写期间专门锁定它——任何试图访问这个表的查询都将等到重写完成。
Consider changing type to text and using check constraint for limiting size — changing constraint would not rewrite or lock a table.
考虑将类型更改为文本并使用检查约束来限制大小——更改约束不会重写或锁定表。
回答by ulric260
From PostgreSQL 9.2 Relase NotesE.15.3.4.2
来自PostgreSQL 9.2 发行说明E.15.3.4.2
Increasing the length limit for a varchar or varbit column, or removing the limit altogether, no longer requires a table rewrite.
增加 varchar 或 varbit 列的长度限制，或完全删除限制，不再需要重写表。
回答by Shiv
Changing the Column Size in Postgresql 9.1 version
在 Postgresql 9.1 版本中更改列大小
During the Column chainging the varchar size to higher values, table re write is required during this lock will be held on table and user table not able access till table re-write is done.
在将 varchar 大小链接到更高值的列期间，在此锁定期间需要重新写入表，并且用户表将无法访问，直到表重新写入完成。
Table Name :- userdata Column Name:- acc_no
表名：- userdata 列名：- acc_no
ALTER TABLE userdata ALTER COLUMN acc_no TYPE varchar(250);
ALTER TABLE userdata ALTER COLUMN acc_no TYPE varchar(250);
回答by jitendra varshney
you can use this below sql commandALTER TABLE client_details
ALTER COLUMN name TYPE varchar(200)
你可以在 sql 命令下面使用这个ALTER TABLE client_details
ALTER COLUMN name TYPE varchar(200)
