postgresql 将 varchar 字段的类型更改为整数：“无法自动转换为整数类型”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13170570/
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
Change type of varchar field to integer: "cannot be cast automatically to type integer"
提问by itsols
I have a small table and a certain field contains the type "character varying". I'm trying to change it to "Integer" but it gives an error that casting is not possible.
我有一个小表，某个字段包含“字符变化”类型。我正在尝试将其更改为“ Integer”，但它给出了无法进行转换的错误。
Is there a way around this or should I just create another table and bring the records into it using a query.
有没有办法解决这个问题，或者我应该创建另一个表并使用查询将记录带入其中。
The field contains only integer values.
该字段仅包含整数值。
回答by Craig Ringer
There is no implicit (automatic) cast from textor varcharto integer(i.e. you cannot pass a varcharto a function expecting integeror assign a varcharfield to an integerone), so you must specify an explicit cast using ALTER TABLE ... ALTER COLUMN ... TYPE ... USING:
没有从text或varchar到的隐式（自动）强制转换integer（即您不能将 a 传递varchar给期望integer或将varchar字段分配给一个的函数integer），因此您必须使用ALTER TABLE ... ALTER COLUMN ... TYPE指定显式强制转换。 .. 使用：
ALTER TABLE the_table ALTER COLUMN col_name TYPE integer USING (col_name::integer);
Note that you may have whitespace in your text fields; in that case, use:
请注意，您的文本字段中可能有空格；在这种情况下，请使用：
ALTER TABLE the_table ALTER COLUMN col_name TYPE integer USING (trim(col_name)::integer);
to strip white space before converting.
在转换之前去除空白。
This shoud've been obvious from an error message if the command was run in psql, but it's possible PgAdmin-III isn't showing you the full error. Here's what happens if I test it in psqlon PostgreSQL 9.2:
如果命令在 中运行psql，这应该从错误消息中很明显，但是 PgAdmin-III 可能没有向您显示完整的错误。如果我psql在 PostgreSQL 9.2 上测试它会发生以下情况：
=> CREATE TABLE test( x varchar );
CREATE TABLE
=> insert into test(x) values ('14'), (' 42 ');
INSERT 0 2
=> ALTER TABLE test ALTER COLUMN x TYPE integer;
ERROR: column "x" cannot be cast automatically to type integer
HINT: Specify a USING expression to perform the conversion.
=> ALTER TABLE test ALTER COLUMN x TYPE integer USING (trim(x)::integer);
ALTER TABLE
Thanks @muistooshort for adding the USINGlink.
感谢@muistooshort 添加USING链接。
See also this related question; it's about Rails migrations, but the underlying cause is the same and the answer applies.
另请参阅此相关问题；这是关于 Rails 迁移，但根本原因是相同的，答案也适用。
If the error still occurs, then it may be related not to column values, but indexes over this column or column default values might fail typecast. Indexes need to be dropped before ALTER COLUMN and recreated after. Default values should be changed appropriately.
如果错误仍然发生，那么它可能与列值无关，但此列或列默认值上的索引可能无法类型转换。索引需要在 ALTER COLUMN 之前删除并在之后重新创建。应适当更改默认值。
回答by bibangamba
thisworked for me.
这对我有用。
change varchar column to int
将 varchar 列更改为 int
change_column :table_name, :column_name, :integer
got:
得到了：
PG::DatatypeMismatch: ERROR: column "column_name" cannot be cast automatically to type integer
HINT: Specify a USING expression to perform the conversion.
chnged to
改为
change_column :table_name, :column_name, 'integer USING CAST(column_name AS integer)'
回答by Nesha Zoric
You can do it like:
你可以这样做：
change_column :table_name, :column_name, 'integer USING CAST(column_name AS integer)'
or try this:
或者试试这个：
change_column :table_name, :column_name, :integer, using: 'column_name::integer'
If you are interested to find more about this topic read this article: https://kolosek.com/rails-change-database-column
如果您有兴趣了解有关此主题的更多信息，请阅读这篇文章：https: //kolosek.com/rails-change-database-column
回答by Subhash Chandra
Try this, it will work for sure.
试试这个，它肯定会起作用。
When writing Rails migrations to convert a string column to an integer you'd usually say:
在编写 Rails 迁移以将字符串列转换为整数时，您通常会说：
change_column :table_name, :column_name, :integer
However, PostgreSQL will complain:
但是，PostgreSQL 会抱怨：
PG::DatatypeMismatch: ERROR: column "column_name" cannot be cast automatically to type integer
HINT: Specify a USING expression to perform the conversion.
The "hint" basically tells you that you need to confirm you want this to happen, and how data shall be converted. Just say this in your migration:
“提示”基本上告诉您，您需要确认您希望这种情况发生，以及数据将如何转换。只需在您的迁移中这样说：
change_column :table_name, :column_name, 'integer USING CAST(column_name AS integer)'
The above will mimic what you know from other database adapters. If you have non-numeric data, results may be unexpected (but you're converting to an integer, after all).
以上将模仿您从其他数据库适配器中了解到的内容。如果您有非数字数据，结果可能会出乎意料（但毕竟您正在转换为整数）。
回答by Valdenir Antoglioli Junior
I got the same problem. Than I realized I had a default string value for the column I was trying to alter. Removing the default value made the error go away :)
我遇到了同样的问题。比我意识到我尝试更改的列有一个默认字符串值。删除默认值使错误消失:)
回答by Sandip Rajput
If you are working on development environment(or on for production env. it may be backup your data) then first to clear the data from the DB field or set the value as 0.
如果您正在开发环境（或用于生产环境。它可能是备份您的数据）然后首先从 DB 字段中清除数据或将值设置为 0。
UPDATE table_mame SET field_name= 0;
After that to run the below query and after successfully run the query, to the schemamigration and after that run the migrate script.
之后运行以下查询并成功运行查询后，到 schemamigration，然后运行 migrate 脚本。
ALTER TABLE table_mame ALTER COLUMN field_name TYPE numeric(10,0) USING field_name::numeric;
I think it will help you.
我认为它会帮助你。
回答by webrama.pl
If you've accidentally or not mixed integers with text data you should at first execute below update command (if not above alter table will fail):
如果您不小心或未将整数与文本数据混合，您应该首先执行下面的更新命令（如果不在上面，alter table 将失败）：
UPDATE the_table SET col_name = replace(col_name, 'some_string', '');
