将varchar字段的类型更改为整数:不能自动转换为整数类型;

我有一个小表和一个特定的字段包含类型“性格不同”。我试图将其更改为“整数”，但它给出了一个错误，即强制转换是不可能的。

是否有办法解决这个问题，或者我应该创建另一个表，并使用查询将记录带入其中。

该字段只包含整数值。

272117 次浏览
小开
最佳答案

没有从text或varchar到integer的隐式(自动)强制转换(即你不能将varchar传递给期望integer的函数，也不能将varchar字段分配给integer字段)，所以你必须使用修改表…修改列…类型……使用指定一个显式强制转换:

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之前删除，在ALTER COLUMN之后重新创建。应适当更改默认值。

小开

如果你不小心把整数和文本数据混合了，你应该首先执行下面的update命令(如果不是上面的alter表将失败):

UPDATE the_table SET col_name = replace(col_name, 'some_string', '');
小开

这对我有用。

将varchar column更改为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)'
小开

如果您在开发环境(或生产环境)中工作。它可能是备份您的数据)，然后首先从DB字段清除数据或设置值为0。

UPDATE table_mame SET field_name= 0;

然后运行下面的查询，成功运行查询后，运行模式迁移，然后运行迁移脚本。

ALTER TABLE table_mame ALTER COLUMN field_name TYPE numeric(10,0) USING field_name::numeric;

我想这对你有帮助。

小开

试试这个，一定会有用的。

当编写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.

“提示”基本上告诉您需要确认希望发生这种情况，以及如何转换数据。在您的迁移中只需这样说:

change_column :table_name, :column_name, 'integer USING CAST(column_name AS integer)'

以上内容将模拟您从其他数据库适配器了解到的内容。如果您使用的是非数字数据，结果可能会出乎意料(但毕竟您正在转换为整数)。

小开
我也有同样的问题。 然后我意识到我有一个默认字符串值的列，我试图改变。 删除默认值使错误消失:)

小开

你可以这样做:

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

小开

我也有同样的问题。我开始重置列的默认值。

change_column :users, :column_name, :boolean, default: nil
change_column :users, :column_name, :integer, using: 'column_name::integer', default: 0, null: false
小开

字符变化中的空字符串或空值

如果你有一个包含空字符串或null的列，你可能会遇到错误消息:

invalid input syntax for type integer: ""

解决方案

使用coalesce和nullif来获取空字符串，使用nulls来获取零值。

ALTER TABLE peopleGroup ALTER numberPeople TYPE INT USING (cast ( coalesce( nullif( trim(numberPeople), '' ), '0' ) as integer ))

提交答案