PostgreSQL中integer与boolean比较报错的解决咨询

问题核心：PostgreSQL默认不支持integer类型与boolean类型直接使用=或<>运算符进行比较——即便存在int到bool的隐式转换，也不会在运算符匹配时自动触发，因此需要手动创建对应的运算符。

解决方案：创建自定义比较运算符 #

1. 先创建用于比较的底层函数 #

这些函数负责将integer转换为boolean后执行比较逻辑：

-- 处理 integer = boolean 的比较
CREATE OR REPLACE FUNCTION integer_equals_boolean(int_val integer, bool_val boolean) RETURNS boolean AS $$
BEGIN
    RETURN (int_val::boolean) = bool_val;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 处理 boolean = integer 的反向比较（适配左右值顺序不同的场景）
CREATE OR REPLACE FUNCTION boolean_equals_integer(bool_val boolean, int_val integer) RETURNS boolean AS $$
BEGIN
    RETURN bool_val = (int_val::boolean);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 处理 integer <> boolean 的不等比较
CREATE OR REPLACE FUNCTION integer_not_equal_boolean(int_val integer, bool_val boolean) RETURNS boolean AS $$
BEGIN
    RETURN (int_val::boolean) <> bool_val;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 处理 boolean <> integer 的反向不等比较
CREATE OR REPLACE FUNCTION boolean_not_equal_integer(bool_val boolean, int_val integer) RETURNS boolean AS $$
BEGIN
    RETURN bool_val <> (int_val::boolean);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

2. 绑定函数到对应的运算符上 #

创建=和<>运算符，让PostgreSQL识别integer与boolean之间的比较操作：

-- 注册 integer = boolean 运算符
CREATE OPERATOR = (
    LEFTARG = integer,
    RIGHTARG = boolean,
    PROCEDURE = integer_equals_boolean,
    COMMUTATOR = =,
    NEGATOR = <>,
    RESTRICT = eqsel,
    JOIN = eqjoinsel,
    HASHES,
    MERGES
);

-- 注册 boolean = integer 运算符（支持交换律）
CREATE OPERATOR = (
    LEFTARG = boolean,
    RIGHTARG = integer,
    PROCEDURE = boolean_equals_integer,
    COMMUTATOR = =,
    NEGATOR = <>,
    RESTRICT = eqsel,
    JOIN = eqjoinsel,
    HASHES,
    MERGES
);

-- 注册 integer <> boolean 运算符
CREATE OPERATOR <> (
    LEFTARG = integer,
    RIGHTARG = boolean,
    PROCEDURE = integer_not_equal_boolean,
    COMMUTATOR = <>,
    NEGATOR = =,
    RESTRICT = neqsel,
    JOIN = neqjoinsel,
    HASHES,
    MERGES
);

-- 注册 boolean <> integer 运算符
CREATE OPERATOR <> (
    LEFTARG = boolean,
    RIGHTARG = integer,
    PROCEDURE = boolean_not_equal_integer,
    COMMUTATOR = <>,
    NEGATOR = =,
    RESTRICT = neqsel,
    JOIN = neqjoinsel,
    HASHES,
    MERGES
);

替代方案：在MyBatis层面手动转换 #

如果不想修改数据库配置，也可以在Mapper的SQL语句中显式添加类型转换，比如：

• 将Boolean参数转为integer：damaged = #{damaged}::integer
• 将integer列转为boolean：damaged::boolean = #{damaged}

这种方式需要逐个修改涉及该列的SQL，通用性不如数据库层面的运算符配置。

内容的提问来源于stack exchange，提问作者mikeb