将 varchar 字段的类型更改为整数:“无法自动转换为整数类型" [英] Change type of varchar field to integer: "cannot be cast automatically to type integer"

问题描述

我有一个小表格，某个字段包含字符变化"类型.我正在尝试将其更改为整数"，但它给出了无法进行强制转换的错误.

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.

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

Is there a way around this or should I just create another table and bring the records into it using a query.

该字段仅包含整数值.

推荐答案

没有从 text 或 varchar 到 integer(即您不能将 varchar 传递给需要 integer 的函数或将 varchar 字段分配给 integer一个)，因此您必须使用 ALTER TABLE ... ALTER 指定显式转换列...类型...使用:

There is no implicit (automatic) cast from text or varchar to integer (i.e. you cannot pass a varchar to a function expecting integer or assign a varchar field to an integer one), so you must specify an explicit cast using ALTER TABLE ... ALTER COLUMN ... TYPE ... USING:

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);

在转换前去除空白.

如果命令是在 psql 中运行的，那么从错误消息中这应该很明显，但是 PgAdmin-III 可能没有向您显示完整的错误.如果我在 PostgreSQL 9.2 上的 psql 中测试它会发生以下情况:

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 psql on 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        

感谢@muistooshort 添加USING 链接.

Thanks @muistooshort for adding the USING link.

另见这个相关问题；这是关于 Rails 迁移，但根本原因是相同的，答案也适用.

See also this related question; it's about Rails migrations, but the underlying cause is the same and the answer applies.

如果错误仍然发生，那么它可能与列值无关，但此列或列默认值上的索引可能无法类型转换.索引需要在 ALTER COLUMN 之前删除并在之后重新创建.应适当更改默认值.

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.

这篇关于将 varchar 字段的类型更改为整数:“无法自动转换为整数类型"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！