Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit



SELECT 1


CREATE table keyword
(
  topic_id INT  NOT NULL GENERATED ALWAYS AS IDENTITY,
  topic    TEXT NOT NULL
);

CREATE TABLE


INSERT INTO keyword (topic) VALUES
('basketball'),
('music'),
('food'),
('war');

INSERT 0 4


CREATE TABLE post
(
  post_id INT  NOT NULL GENERATED ALWAYS AS IDENTITY,
  content TEXT NOT NULL
);

CREATE TABLE


INSERT INTO post (content) VALUES
('A typhoon warning has been issued in southern Japan'),
('We are going to play neither basketball nor volleyball'),
('I am indulging in both the delightful music and delectable food'),
('That basketball player fouled again');

INSERT 0 4


SELECT 
  post_id, 
  
  COALESCE
  (
    array_to_string(array_agg(DISTINCT topic_id ORDER BY topic_id), ','), 
    'Vague!'
  ) AS topic,

  LENGTH(array_to_string(array_agg(DISTINCT topic_id ORDER BY topic_id), ',')),

  LENGTH(NULL) AS "Length of NULL",  -- <<== LENGTH(NULL) is always NULL, and NOT zero 

  PG_TYPEOF(NULL) AS "Type of NULL",
  
  PG_TYPEOF
  (
    array_to_string(array_agg(DISTINCT topic_id ORDER BY topic_id), ',')
  )
FROM 
(
  SELECT p.post_id, k.topic_id
  FROM post p 
  LEFT JOIN keyword k 
    ON LOWER(content) LIKE '% ' || topic || ' %' 
    OR content LIKE topic || ' %' OR content LIKE '% ' || topic
) a
GROUP BY post_id
ORDER BY post_id;


post_id
topic
length
Length of NULL
Type of NULL
pg_typeof


1

0
null
unknown
text

2
1
1
null
unknown
text

3
2,3
3
null
unknown
text

4
1
1
null
unknown
text



SELECT 4


SELECT 
  post_id, 
  CASE
    WHEN
      (array_to_string(array_agg(DISTINCT topic_id ORDER BY topic_id), ',') IS NULL) 
        THEN 'Vague!'
    WHEN
      (array_to_string(array_agg(DISTINCT topic_id ORDER BY topic_id), ',') = '') 
        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 post p 
  LEFT JOIN keyword k 
    ON LOWER(content) LIKE '% ' || topic || ' %' 
    OR content LIKE topic || ' %' OR content LIKE '% ' || topic
) a
GROUP BY post_id
ORDER BY post_id;


post_id
topic


1
Vague!

2
1

3
2,3

4
1



SELECT 4
post_id	topic	length	Length of NULL	Type of NULL	pg_typeof
1		0	null	unknown	text
2	1	1	null	unknown	text
3	2,3	3	null	unknown	text
4	1	1	null	unknown	text