如何将表列转换为另一种数据类型 [英] How to convert a table column to another data type

问题描述

我在Postgres数据库中有一列类型为Varchar的列，我本想是整数...现在我想更改它们，不幸的是，这似乎不适用于我的rails迁移.

I have a column with the type of Varchar in my Postgres database which I meant to be integers... and now I want to change them, unfortunately this doesn't seem to work using my rails migration.

change_column :table1, :columnB, :integer

哪个似乎输出此SQL:

Which seems to output this SQL:

ALTER TABLE table1 ALTER COLUMN columnB TYPE integer

所以我尝试这样做:

execute 'ALTER TABLE table1 ALTER COLUMN columnB TYPE integer USING CAST(columnB AS INTEGER)'

但是强制转换在这种情况下不起作用，因为某些列为空...

but cast doesn't work in this instance because some of the column are null...

有什么想法吗?

错误:

PGError: ERROR:  invalid input syntax for integer: ""
: ALTER TABLE table1 ALTER COLUMN columnB TYPE integer USING CAST(columnB AS INTEGER)

Postgres v8.3

Postgres v8.3

推荐答案

听起来像是问题在于您的表中有空字符串.您可能需要使用case语句来处理这些问题，例如:

It sounds like the problem is that you have empty strings in your table. You'll need to handle those, probably with a case statement, such as:

execute %{ALTER TABLE "table1" ALTER COLUMN columnB TYPE integer USING CAST(CASE columnB WHEN '' THEN NULL ELSE columnB END AS INTEGER)}

更新:根据更新的问题完全重写.

Update: completely rewritten based on updated question.

这篇关于如何将表列转换为另一种数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！