Create a PostgreSQL view from a unique list of exploded data

When I make this request:

SELECT mycol FROM mytable WHERE mycol IS NOT NULL;

I get results like this:

foo / bar / hello
foo / hello
foo / bar

Each line has values separated with / (space, slash, space).

How to create a view with a list of unique values with count ?

bar 2
foo 3
hello 2

Answer

You can split the string to rows with regexp_split_to_table() in a lateral join, then aggregate:

select x.val, count(*) cnt
from mytable t
cross join lateral regexp_split_to_table(t.mycol, 's/s') as x(val)
group by x.val

Demo on DB Fiddle:

val   | cnt
:---- | --:
bar   |   2
foo   |   3
hello |   2