Postgres将整数列改变为布尔值

本文是小编为大家收集整理的关于Postgres将整数列改变为布尔值的处理/解决方法，可以参考本文帮助大家快速定位并解决问题，中文翻译不准确的可切换到English标签页查看源文。

中文
English

问题描述

我有一个整数而不是null默认为0，我需要将其更改为bool.

这就是我正在使用的:

ALTER TABLE mytabe 
ALTER mycolumn TYPE bool 
USING 
    CASE 
        WHEN 0 THEN FALSE 
        ELSE TRUE 
    END;

但是我得到了:

ERROR:  argument of CASE/WHEN must be type boolean, not type integer

********** Error **********

ERROR: argument of CASE/WHEN must be type boolean, not type integer
SQL state: 42804

有什么想法吗?

谢谢.

其他推荐答案

Postgres可以自动将整数铸造为布尔值.关键短语是

using some_col_name::boolean
-- here some_col_name is the column you want to do type change

上面的答案是正确的

ALTER TABLE mytabe ALTER COLUMN mycolumn DROP DEFAULT;
ALTER TABLE mytabe ALTER mycolumn TYPE bool USING mycolumn::boolean;
ALTER TABLE mytabe ALTER COLUMN mycolumn SET DEFAULT FALSE;

其他推荐答案

还检查您的列上没有任何检查约束:

[...] CONSTRAINT blabla CHECK ((field = ANY (ARRAY[0, 1])))

否则，您将使用cannot convert to boolean type

alter命令错误

本文地址：https://www.itbaoku.cn/post/597526.html

百度翻译此文有道翻译此文

问题描述

I've a field that is INTEGER NOT NULL DEFAULT 0 and I need to change that to bool.

This is what I am using:

ALTER TABLE mytabe 
ALTER mycolumn TYPE bool 
USING 
    CASE 
        WHEN 0 THEN FALSE 
        ELSE TRUE 
    END;

But I am getting:

ERROR:  argument of CASE/WHEN must be type boolean, not type integer

********** Error **********

ERROR: argument of CASE/WHEN must be type boolean, not type integer
SQL state: 42804

Any idea?

Thanks.

其他推荐答案

Postgres can automatically cast integer to boolean. The key phrase is

using some_col_name::boolean
-- here some_col_name is the column you want to do type change

Above Answer is correct that helped me Just one modification instead of case I used type casting

ALTER TABLE mytabe ALTER COLUMN mycolumn DROP DEFAULT;
ALTER TABLE mytabe ALTER mycolumn TYPE bool USING mycolumn::boolean;
ALTER TABLE mytabe ALTER COLUMN mycolumn SET DEFAULT FALSE;

其他推荐答案

Also check that you don't have any CHECK constraint on you column like:

[...] CONSTRAINT blabla CHECK ((field = ANY (ARRAY[0, 1])))

otherwise you alter command will error with a cannot convert to boolean type

上一篇：主键应该总是无符号的？

下一篇：将PostgreSQL数据库迁移到SQL Server数据库的最简单方法