Postgres SQL - Compare Count of rows, returning where a < b or b doesn't exist
You can get COUNT of each tag by using next query;




SELECT hash,
       SUM(CASE WHEN val ILIKE 'dht\_%' THEN 1 ELSE 0 END) dht,
       SUM(CASE WHEN val ILIKE 'thumb_%' THEN 1 ELSE 0 END) thumb
FROM   tags
GROUP BY hash;



hash             | dht | thumb
:--------------- | --: | ----:
8dbb1085750696c5 |   3 |     4
b119ceb321058d7c |   0 |     6



Then apply filter conditions:




WITH CTG AS
(
    SELECT hash,
           SUM(CASE WHEN val ILIKE 'dht\_%' THEN 1 ELSE 0 END) dht,
           SUM(CASE WHEN val ILIKE 'thumb_%' THEN 1 ELSE 0 END) thumb
    FROM   tags
    GROUP BY hash
)
SELECT hash, dht, thumb
FROM   CTG
WHERE  (thumb &gt; dht)
OR     (thumb &gt;= 1 AND dht = 0);



hash             | dht | thumb
:--------------- | --: | ----:
8dbb1085750696c5 |   3 |     4
b119ceb321058d7c |   0 |     6



The condition can be further simplified to:

WHERE  (thumb &gt; dht) ;


And it might be more efficient as a subquery (derived table) than a CTE, if the rows (that are produced by the CTE/derived table) are very many:

SELECT hash, dht, thumb
FROM
(
    SELECT hash,
           SUM(CASE WHEN val ILIKE 'dht\_%' THEN 1 ELSE 0 END) dht,
           SUM(CASE WHEN val ILIKE 'thumb_%' THEN 1 ELSE 0 END) thumb
    FROM   tags
    GROUP BY hash
)
    AS CTG
WHERE  (thumb &gt; dht) ;


dbfiddle here