PostgreSQL两表关联查询报错：运算符不存在问题解决

问题背景 #

现有两张PostgreSQL表A和B：

表A结构与数据 #

表B结构与数据 #

需求：统计当B.geo为'ny'时，每个A.host_id在B中的出现次数，预期输出：

当前使用的查询语句：

select 
A.host_id, 
A.host_names,
count(B.host_id) as count
from B
inner join A
on A.host_id :: varchar(255) like '%' + B.host_id :: varchar(255) + '%' 
where B.geo like '%ny%'
GROUP BY A.host_id, A.host_names
ORDER BY A.host_id;

收到错误：

ERROR:  operator does not exist: unknown + text
LINE 7: on B.host_id like '%' + A...
                                        ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts. 

错误原因及解决步骤 #

1. 语法错误修复 #

PostgreSQL中字符串拼接不能用+，必须用||操作符。这是导致当前报错的直接原因。但即使修复这个，原SQL的关联逻辑仍有问题——like '%1%'会匹配包含1的所有字符串（比如11、12），导致统计结果不准确。

2. 关联逻辑修正 #

要准确匹配B.host_id中完整的host_id值，建议将B中的逗号分隔字符串转换为数组，再判断A.host_id是否存在于数组中：

• 使用string_to_array(B.host_id, ',')将字符串拆分为文本数组
• 使用A.host_id::text = ANY(string_to_array(B.host_id, ','))判断A的host_id是否在数组内（需要统一类型）

3. 最终正确查询语句 #

SELECT 
    A.host_id,
    A.name,
    COUNT(B.id) AS count
FROM A
LEFT JOIN B 
    ON A.host_id::text = ANY(string_to_array(B.host_id, ','))
    AND B.geo = 'ny'
WHERE B.id IS NOT NULL  -- 过滤掉没有匹配的A记录
GROUP BY A.host_id, A.name
ORDER BY A.host_id;

或者使用INNER JOIN写法，效果一致：

SELECT 
    A.host_id,
    A.name,
    COUNT(B.id) AS count
FROM B
INNER JOIN A 
    ON A.host_id::text = ANY(string_to_array(B.host_id, ','))
WHERE B.geo = 'ny'
GROUP BY A.host_id, A.name
ORDER BY A.host_id;

结果验证 #

以上查询会正确统计每个A.host_id在B.geo='ny'时的出现次数，与预期输出完全一致。

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