我有一个小表格，其中某个字段包含类型为“character varying”。我试图将其更改为“Integer”，但会出现无法转换的错误。
是否有解决方法，或者我应该只需创建另一个表，并使用查询将记录导入它？
该字段仅包含整数值。
9个回答
355
在编程中，从text或varchar到integer没有隐式（自动）转换（即您不能将varchar传递给期望integer的函数或将varchar字段分配给integer字段），因此您必须使用ALTER TABLE ... ALTER COLUMN ... TYPE ... USING指定显式转换：
ALTER TABLE the_table ALTER COLUMN col_name TYPE integer USING (col_name::integer);
请注意，您的文本字段中可能包含空格；如果是这样，请使用以下方法：
ALTER TABLE the_table ALTER COLUMN col_name TYPE integer USING (trim(col_name)::integer);
在转换之前去除空格。
如果在psql中运行该命令，应该从错误消息中清楚地看到这一点，但是PgAdmin-III可能没有显示完整的错误。以下是我在PostgreSQL 9.2上在psql中测试它时发生的情况：
=> 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
感谢@muistooshort添加了USING链接。
另请参见这个相关问题；它是关于Rails迁移的，但根本原因是相同的，答案适用。
如果错误仍然发生，则可能与列值无关，而与此列上的索引或列默认值失败的类型转换有关。必须在ALTER COLUMN之前删除索引，并在之后重新创建索引。应适当更改默认值。
- Craig Ringer
11
感谢您抽出时间。但我似乎无法让它工作。我尝试了您的ALTER行，但它给我一个错误：“在Using附近的语法错误”。 - itsols
我的语句：ALTER TABLE "tblMenus" ALTER COLUMN "MID" USING (trim("MID")::integer); - itsols
1@itsols 完全是我的错误；我看到你的评论后立即进行了更正。请查看修订版。在演示代码中是正确的，只是在开头的通用示例中不正确。 - Craig Ringer
非常感谢！这个答案帮我省去了很多麻烦和时间。我不明白为什么phppgadmin和pgadmin都没有这项功能... - itsols
感谢您在博客上提供的更新。即使是我也不喜欢wx，这就是我从CodeBlocks转到Lazarus的原因。我无法在Ubuntu或Windows上正确地使用wx。但另一方面，我想Lazarus也在使用它，但它可以直接使用。此外，我不喜欢QT，因为许可证和可用性问题。哎呀，话题有些偏了;) 再次感谢！ - itsols
显示剩余6条评论
79
这个对我有用。
将varchar列更改为int
change_column :table_name, :column_name, :integer
收到：
PG::DatatypeMismatch: ERROR: column "column_name" cannot be cast automatically to type integer
HINT: Specify a USING expression to perform the conversion.
改成
change_column :table_name, :column_name, 'integer USING CAST(column_name AS integer)'
- bibangamba
8
你尝试使用数据完成这个练习了吗？你的数据还完好无损吗？ - itsols
4只要列中的内容是整数，就是的。 - bibangamba
它对我不起作用。我正在使用Ruby 2.2.3和Rails 4.2.3。 - Thinh D. Bui
@ThinhD.Bui - 对我来说没问题，2.3.0版本，rails 4.2.6。 - Philip
1小心使用默认值 - Francisco Quintero
显示剩余3条评论
28
您可以这样做：
change_column :table_name, :column_name, 'integer USING CAST(column_name AS integer)'
或者尝试这个：
change_column :table_name, :column_name, :integer, using: 'column_name::integer'
如果您对此话题感兴趣，请阅读本文：https://kolosek.com/rails-change-database-column
- Nesha Zoric
9
请试一下，这样肯定会起作用。
当编写Rails迁移以将字符串列转换为整数时，通常会使用以下命令：
change_column :table_name, :column_name, :integer
然而，PostgreSQL会抱怨：
PG::DatatypeMismatch: ERROR: column "column_name" cannot be cast automatically to type integer
HINT: Specify a USING expression to perform the conversion.
"提示(hint)"基本上是告诉你需要确认你想要发生这种情况，并且数据应该如何转换。在您的迁移中，只需这样说："
change_column :table_name, :column_name, 'integer USING CAST(column_name AS integer)'
以上内容将模仿其他数据库适配器的操作方式。如果您有非数字数据，则可能会出现意外结果（但毕竟您正在转换为整数）。
- Subhash Chandra
2
我只想再补充一点，小心使用change_column。它是不可逆的。我建议在迁移中使用up和down来使其可逆。 - Mukesh Kumar Gupta
4出现了“PG::InvalidTextRepresentation: ERROR: invalid input syntax for integer: ""”错误。 - Shaig Khaligli
8
我遇到了相同的问题。 然后我意识到我在尝试更改的列中有一个默认的字符串值。 移除默认值后，错误消失了 :)
- Valdenir Antoglioli Junior
1
此列上已存在的索引也可能是一个问题。在 ALTER 命令执行之前，这些索引需要被删除，然后再进行重建。 - Envek
7
我遇到了同样的问题。我开始重置列的默认设置。
change_column :users, :column_name, :boolean, default: nil
change_column :users, :column_name, :integer, using: 'column_name::integer', default: 0, null: false
- Maxime Boué
1
谢谢。在我的情况下，转换为
boolean 是不必要的。 - Martin Zinovsky2
如果您正在开发环境下工作（或是生产环境下需要备份数据），那么请先清除数据库字段中的数据，或者将其值设置为0。
UPDATE table_mame SET field_name= 0;
接下来运行以下查询，成功运行查询后，进行模式迁移，然后运行迁移脚本。
ALTER TABLE table_mame ALTER COLUMN field_name TYPE numeric(10,0) USING field_name::numeric;
我认为这会对你有所帮助。
- Sandip Rajput
1
如果您不小心或者有意将整数和文本数据混合在一起，那么首先执行以下更新命令（如果没有执行上述alter table命令会失败）：
UPDATE the_table SET col_name = replace(col_name, 'some_string', '');
- webrama.pl
1
3如果你想去除不必要的字符和空格，最好使用类似于
regexp_replace(col_name, '[^0-9.]','','g') 这样的东西。但如果你想保留 NaN、Inf 和 10E42 科学计数法，则需要使用更加复杂的方法。 - Craig Ringer0
字符变量中的空字符串或null值
如果您有一个包含空字符串或null值的列，您可能会遇到以下错误信息：
invalid input syntax for type integer: ""
解决方案
使用coalesce和nullif将空字符串和null值转换为零值。
ALTER TABLE peopleGroup ALTER numberPeople TYPE INT USING (cast ( coalesce( nullif( trim(numberPeople), '' ), '0' ) as integer ))
- Tim G
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接
SELECT列表，而不是依赖于列序号位置。话虽如此，答案中给出的方法将保留列位置。 - Craig Ringer