PostgreSQL复合键查询遇record=text运算符错误，求正确写法

原SQL语句（用于同时匹配route和is_deleted字段）：

select * 
from route r 
where (r.route, r.is_deleted) = any(string_to_array('ABBEYARD, VICTORIA, AUSTRALIA (CBR), false',';'));

执行后触发的错误：

SQL Error [42883]: ERROR: operator does not exist: record = text
Hint: No operator matches the given name and argument type(s). You might need to add 
explicit type casts.
Position: 53

错误原因 #

string_to_array返回的是text类型数组，而(r.route, r.is_deleted)是PostgreSQL的record（记录）类型，两者没有默认匹配的运算符，导致报错。

修正方案 #

方案一：显式转换数组元素为元组类型 #

将输入字符串拆分后，把每个元素转换为与(route, is_deleted)结构一致的元组数组：

select * 
from route r 
where (r.route, r.is_deleted) = any(
    -- 直接构造匹配结构的元组数组，显式指定类型
    array[('ABBEYARD, VICTORIA, AUSTRALIA (CBR)', false)]::(text, boolean)[]
);

如果是多组分号分隔的匹配条件（比如'路线1,false;路线2,true'），可以先拆分再转换：

select * 
from route r 
where (r.route, r.is_deleted) = any(
    string_to_array('ABBEYARD, VICTORIA, AUSTRALIA (CBR),false;CENTRAL STATION,true', ';')
    ::(text, boolean)[]
);

⚠️ 注意：要确保每个分号分隔的子串格式严格匹配"路线值,布尔值"，布尔值不要加空格。

方案二：拆分字符串为行后关联查询（更灵活） #

如果路线值本身包含逗号，上述方案会失效，此时可以拆分字符串为单独的行，再解析字段后关联查询：

select r.* 
from route r
inner join (
    select 
        -- 提取路线值（从开头到倒数第一个逗号前的内容）
        regexp_replace(item, ',[^,]+$', '') as route_val,
        -- 提取布尔值（最后一个逗号后的内容）并转换类型
        (split_part(item, ',', -1))::boolean as is_deleted_val
    from unnest(string_to_array('ABBEYARD, VICTORIA, AUSTRALIA (CBR),false;CENTRAL STATION,true', ';')) as item
) as filter
on r.route = filter.route_val 
and r.is_deleted = filter.is_deleted_val;

这种方式能兼容路线值包含逗号的场景，通过正则表达式或更精准的字符串拆分逻辑提取字段。

内容的提问来源于stack exchange，提问作者Ephraim Kesaba Silalahi