postgresqlpostgresql-9.4

PostgreSQL count frequency of each keyword in keyword match

Basically I'm checking to see if a column contains any of a list of keywords in an array 

SELECT COUNT(*) FROM table WHERE value ILIKE ANY (ARRAY['%bat%', '%cat%', ...])

Which works fine to give me the total count of columns with those keywords, but I also want to count how many times each of those keywords is hit. Ideally I would end up with 

 Keyword   |   Count
---------------------
 bat       |     4
 cat       |    10

et cetera. Any ideas? Thanks!

Solution

  • You'll be better off with regular expressions for testing full words within a text:

    with
  words(word) as (values ('bat'), ('cat'))

select w.word, count(*)
from words w
join table t on (t.value ~* ('\m'||w.word||'\M'))
group by w.word;

    Replace ~* with ~ if you need case-sensitive searches.