将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屋!