MySQL迁移至PostgreSQL:多对多关联按指定颜色集合过滤条目的SQL报错咨询
报错核心原因
- MySQL 存在隐式类型转换机制:执行时会自动把
c.name IN ('green', 'blue')这类布尔判断结果转换为1(判断为真)/0(判断为假)的整数,因此可以直接传入SUM()函数完成累加计算。 - PostgreSQL 采用严格类型校验规则,不会对布尔值做隐式数值转换,
SUM()函数仅支持数值类型入参,直接传入布尔表达式就会触发function sum(boolean) does not exist的报错,需要手动做类型适配。
适配PostgreSQL的正确实现写法
两种写法都能完全匹配查询需求(仅返回关联颜色完全属于green、blue范围的条目,自动排除存在范围外颜色、无关联颜色的条目):
写法1:显式类型转换对齐MySQL逻辑
给布尔判断结果添加强转::int,把布尔值转为对应的整数后再累加,和原有MySQL的执行逻辑完全一致:
SELECT i.* FROM items i JOIN items_colors ic ON ic.item_id = i.id JOIN colors c ON c.id = ic.color_id GROUP BY i.id -- 对比条目关联的总颜色数 和 属于指定范围的颜色数是否相等 HAVING COUNT(*) = SUM( (c.name IN ('green', 'blue'))::int )
等价判断写法:只要统计到条目关联的范围外颜色总数为0,就符合返回条件
HAVING SUM( (c.name NOT IN ('green', 'blue'))::int ) = 0
写法2:使用PostgreSQL原生布尔聚合函数
不需要做类型转换,直接用BOOL_AND聚合判断分组内所有关联颜色是否都满足“在指定范围内”的条件,逻辑更直观:
SELECT i.* FROM items i JOIN items_colors ic ON ic.item_id = i.id JOIN colors c ON c.id = ic.color_id GROUP BY i.id -- 所有关联颜色都在指定范围时,聚合结果才为真 HAVING BOOL_AND(c.name IN ('green', 'blue'))
内容的提问来源于stack exchange,提问作者Etienne
相关产品推荐
相关产品推荐

