是否有一种方法可以使用通配符查询PostgreSQL hstore字段中某些键的存在

Question

我想根据hstore列中某些键的存在(或不存在)来过滤PostgreSQL 9.0表中的记录。现在，我列出了所有候选人，如下所示:

SELECT AVG(array_upper(%# tags,1)) FROM nodes 
WHERE array_upper(%# tags,1) > 0 AND NOT 
tags?|ARRAY['gnis:state_id','gnis:id','gnis:Class','gnis:County',
'gnis:ST_num','gnis:ST_alpha','gnis:County_num','gnis:reviewed',
'gnis:feature_id','gnis:county_name','gnis:import_uuid'];

我真正想要做的是计算该列中键值对的平均数量，不包括包含任何以"gnis:"开头的键的键值对。有没有更有效的方法?

Answer 1

据我所知hstore模块不支持您想要的简单形状的通配符。然而，实现这些功能看起来很容易，例如:

WITH excluded_tags AS
(
    SELECT array_agg(key) AS tags
    FROM (SELECT skeys(tags) AS key FROM nodes) k
    WHERE key LIKE 'gnis:%'
)
SELECT avg(array_upper(%# tags, 1))
FROM nodes
WHERE array_upper(%# tags, 1) > 0
    AND NOT tags ?| (SELECT tags FROM excluded_tags);

或更短:

SELECT avg(array_upper(%# tags, 1))
FROM nodes 
WHERE array_upper(%# tags, 1) > 0 AND
    NOT EXISTS (SELECT skeys FROM skeys(tags) WHERE skeys LIKE 'gnis:%');