在SQLDelight(PostgreSQL)中如何正确判断数组参数非空

你遇到的报错是SQLDelight的类型推导机制限制导致的：它无法在CASE表达式的分支中正确识别List类型的绑定参数，因此会抛出类型不匹配的错误。 #

方案1：WHERE条件OR改写（最简洁，推荐） #

直接将列表参数判断逻辑拆成OR并联的条件即可适配SQLDelight的语法，同时支持参数为null、空列表、有值三种场景：

select b.resourceid,
       b.datecreatedutc as basecreated,
       b.datedeletedutc as basedeleted
from productbasetable b
where b.datedeletedutc is null
  -- 参数为null/空列表时返回全量，否则走IN过滤
  and (:resourceIdList is null OR :resourceIdList = EMPTY() OR b.resourceid in :resourceIdList);

如果不需要处理空列表的场景，仅需判断参数为null的情况，删掉:resourceIdList = EMPTY()的判断即可。 #

方案2：UNION ALL拆分（优化索引性能） #

如果resourceid字段建有索引，OR写法可能导致索引失效，可以用UNION ALL拆分两个查询分支，保证索引可以被正常命中：

select b.resourceid,
       b.datecreatedutc as basecreated,
       b.datedeletedutc as basedeleted
from productbasetable b
where b.datedeletedutc is null
  and :resourceIdList is not null
  and :resourceIdList != EMPTY()
  and b.resourceid in :resourceIdList

UNION ALL

select b.resourceid,
       b.datecreatedutc as basecreated,
       b.datedeletedutc as basedeleted
from productbasetable b
where b.datedeletedutc is null
  and (:resourceIdList is null OR :resourceIdList = EMPTY());

注意事项 #

• 需要在SQLDelight的.sq文件中正确声明参数类型，示例：getResourceList(resourceIdList: List<Long>?): Resource，要保证列表泛型和resourceid字段类型完全匹配
• 不需要额外自定义编码/解码逻辑，SQLDelight会自动处理List类型参数的绑定

内容的提问来源于stack exchange，提问作者P. Savrov