如何在 PostgreSQL 8.4 中将列数据类型从字符更改为数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7683359/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to change column datatype from character to numeric in PostgreSQL 8.4
提问by user728630
I am using following query:
我正在使用以下查询：
ALTER TABLE presales ALTER COLUMN code TYPE numeric(10,0);
to change the datatype of a column from character(20)to numeric(10,0)but I am getting the error:
将列的数据类型从 更改为character(20)，numeric(10,0)但出现错误：
column "code" cannot be cast to type numeric
列“代码”不能强制转换为数字类型
回答by mu is too short
You can try using USING:
您可以尝试使用USING：
The optional
USINGclause specifies how to compute the new column value from the old; if omitted, the default conversion is the same as an assignment cast from old data type to new. AUSINGclause must be provided if there is no implicit or assignment cast from old to new type.
可选
USING子句指定如何从旧列值计算新列值；如果省略，则默认转换与从旧数据类型转换为新数据类型的赋值相同。一个USING如果从旧到新类型没有隐含或者赋值转换必须提供条款。
So this might work (depending on your data):
所以这可能有效（取决于您的数据）：
alter table presales alter column code type numeric(10,0) using code::numeric;
-- Or if you prefer standard casting...
alter table presales alter column code type numeric(10,0) using cast(code as numeric);
This will fail if you have anything in codethat cannot be cast to numeric; if the USING fails, you'll have to clean up the non-numeric data by hand before changing the column type.
如果您有任何code不能转换为数字的内容，这将失败；如果 USING 失败，您必须在更改列类型之前手动清理非数字数据。
回答by Patru
If your VARCHARcolumn contains empty strings (which are notthe same as NULLfor PostgreSQL as you might recall) you will have to use something in the line of the following to set a default:
如果VARCHAR列包含空字符串（这是不一样的NULLPostgreSQL的，你可能还记得），您将在下面设置一个默认的行使用的东西：
ALTER TABLE presales ALTER COLUMN code TYPE NUMERIC(10,0)
USING COALESCE(NULLIF(code, '')::NUMERIC, 0);
(found with the help of this answer)
（在这个答案的帮助下找到）
