How do I write a contains query in Postgres that spans two tables?

Question

I'm using Postgres 9.5. I have a table with a label column ...

    Table "public.article"
             Column             |           Type           |                           Modifiers                            
--------------------------------+--------------------------+----------------------------------------------------------------
    ...
 label                          | text                     | 

Then I have another table with specific words ...

mydb=> \d keyword;
                                 Table "public.keyword"
 Column |          Type          |                          Modifiers                           
--------+------------------------+--------------------------------------------------------------
 id     | integer                | not null default nextval('keyword_id_seq'::regclass)
 word   | character varying(200) | not null

How do I write a query that checks if the article's label contains (in a case-insensitive way) one of the words from the keyword table? I'm open to changing the data types of each if that helps speed things up.

Answer 1

You can use ILIKE for case insensitive pattern matching:

SELECT a.label
FROM article AS a
   JOIN keyword AS k
      ON a.label ILIKE '%' || l.word || '%';

If both tables are big, this will be slow, as only a nested loop join can be used. I don't think that can be avoided.