Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table keywords (topic_id int, keyword varchar(100));
insert into keywords values
(1,'basketball'),
(2,'music'),
(3,'food'),
(4,'war')

CREATE TABLE



INSERT 0 4


create table posts (post_id int,	content varchar(100));
insert into posts values
(1,'A typhoon warning has been issued in southern Japan'),
(2,'We are going to play neither basketball nor volleyball'),
(3,'I am indulging in both the delightful music and delectable food'),
(4,'That basketball player fouled again')

CREATE TABLE



INSERT 0 4


SELECT 
      p.post_id, 
      COALESCE(STRING_AGG(k.topic_id::varchar,',' 
                 ORDER BY k.topic_id), 
               'Vague!') AS topic
    FROM Posts p 
    LEFT JOIN Keywords k 
      ON LOWER(p.content) LIKE '% ' || k.keyword || ' %' 
         OR p.content LIKE k.keyword || ' %' 
         OR p.content LIKE '% ' || k.keyword
    GROUP BY p.post_id
    ORDER BY p.post_id;


post_id
topic


1
Vague!

2
1

3
2,3

4
1



SELECT 4


SELECT post_id, 
  CASE WHEN LENGTH(array_to_string(array_agg(DISTINCT topic_id ORDER BY topic_id),
  ',')) = 0 THEN 'Vague!' ELSE array_to_string(array_agg(DISTINCT topic_id ORDER BY topic_id),
  ',') END AS topic
FROM (
    SELECT p.post_id, k.topic_id
    FROM Posts p 
    LEFT JOIN Keywords k 
    ON LOWER(content) LIKE '% ' || keyword || ' %' 
    OR content LIKE keyword || ' %' 
    OR content LIKE '% ' || keyword
  ) a
GROUP BY post_id
ORDER BY post_id


post_id
topic


1
Vague!

2
1

3
2,3

4
1



SELECT 4