在PostgreSQL中将列数据类型从Text更改为Integer [复制]

This question already has an answer here:

这个问题在这里已有答案:

• Rails Migrations: tried to change the type of column from string to integer 6 answers

Rails迁移:尝试将列的类型从字符串更改为整数6个答案

I am using the following query to change the data type of a column from text to integer but getting error:

我使用以下查询将列的数据类型从文本更改为整数但获取错误:

 alter table a.attend alter column terminal TYPE INTEGER ;

ERROR: column "terminal" cannot be cast automatically to type integer

错误:列“终端”无法自动转换为整数类型

1 个解决方案

#1

create table test(id varchar );
insert into test values('1');
insert into test values('11');
insert into test values('12');

select * from test

 --Result--
 id
 character varying
--------------------------
 1
 11
 12

You can see from the above table that I have used the data type – character varying for id column. But it was a mistake because I am always giving integers as id. So using varchar here is a bad practice. So let’s try to change the column type to integer.

您可以从上表中看到我使用了数据类型 - 字符变化为id列。但这是一个错误,因为我总是把整数作为id。所以在这里使用varchar是一种不好的做法。所以让我们尝试将列类型更改为整数。

ALTER TABLE test ALTER COLUMN id TYPE integer;

But it returns:

但它返回:

ERROR: column “id” cannot be cast automatically to type integer SQL state: 42804 Hint: Specify a USING expression to perform the conversion

错误:列“id”无法自动转换为类型整数SQL状态:42804提示:指定USING表达式以执行转换

That means we can’t simply change the data type because data is already there in the column. Since the data is of type character varying Postgres can't expect it as integer though we entered integers only. So now, as Postgres suggested we can use the USING expression to cast our data into integers.

这意味着我们不能简单地更改数据类型,因为列中已存在数据。由于数据类型为字符变化,Postgres不能指望它为整数,尽管我们只输入了整数。所以现在,正如Postgres建议我们可以使用USING表达式将数据转换为整数。

ALTER TABLE test ALTER COLUMN id  TYPE integer USING (id::integer);

It Works.

---

So you should use

所以你应该使用

alter table a.attend alter column terminal TYPE INTEGER  USING (terminal::integer) ;