在 PTT Database 版看到這篇問題: https://www.ptt.cc/bbs/Database/M.1554802071.A.872.html
試解答如下:

create table ithelp190410 (
  id int not null primary key
, tagstr text not null
);

insert into ithelp190410 values
(1, 'tag1;tag2;tag3'),
(2, 'tag2;tag3;tag4'),
(3, 'tag3;tag4;tag5;tag1');

-- string_to_array()
select id
     , tagstr
     , string_to_array(tagstr, ';') as stary
  from ithelp190410;

+----+---------------------+-----------------------+
| id |       tagstr        |         stary         |
+----+---------------------+-----------------------+
|  1 | tag1;tag2;tag3      | {tag1,tag2,tag3}      |
|  2 | tag2;tag3;tag4      | {tag2,tag3,tag4}      |
|  3 | tag3;tag4;tag5;tag1 | {tag3,tag4,tag5,tag1} |
+----+---------------------+-----------------------+
(3 rows)
-- unnest()
select unnest(string_to_array(tagstr, ';')) as un_nest
  from ithelp190410;

+---------+
| un_nest |
+---------+
| tag1    |
| tag2    |
| tag3    |
| tag2    |
| tag3    |
| tag4    |
| tag3    |
| tag4    |
| tag5    |
| tag1    |
+---------+
(10 rows)
-- group by

select elem
     , count(elem)
  from (select unnest(string_to_array(tagstr, ';')) as elem
          from ithelp190410) a
 group by elem
 order by elem; 

+------+-------+
| elem | count |
+------+-------+
| tag1 |     2 |
| tag2 |     2 |
| tag3 |     3 |
| tag4 |     2 |
| tag5 |     1 |
+------+-------+
(5 rows)