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

问题描述

我有一个小表，某个字段包含类型字符变化。我试图将其更改为整数，但它给出一个错误，表明不可能进行转换。

该字段只包含整数值。

没有从 text 或 varchar 到 integer （即，不能将 varchar 传递给期望 integer 或将 varchar 字段分配给整数），因此必须指定一个显式转换 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 
 =>插入到test（x）值（'14'），（'42'）; 
 INSERT 0 2 
 => ALTER TABLE测试ALTER COLUMN x TYPE整数; 
错误：列x不能自动转换为类型整数
提示：指定USING表达式以执行转换。 
 => ALTER TABLE test ALTER COLUMN x TYPE integer USING（trim（x）:: integer）; 
 ALTER TABLE 
  

感谢@muistooshort添加 code>链接。

另请参见此相关问题;它是关于Rails迁移，但根本原因是一样的，答案适用。

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.

The field contains only integer values.

解决方案

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

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

Thanks @muistooshort for adding the USING link.

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

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