Postgresql - Opposite of string_agg

Question

I'm looking for a postgresql function that will do the opposite of string_agg.

I have a movies table where the tags column contains values such as

Action|Adventure|Drama|Horror|Sci-Fi
Action|Horror|Sci-Fi

I would like to get a distinct list of tags from this column, for example

Action
Adventure
Drama
Horror
Sci-Fi

score 9 · Accepted Answer · answered Mar 11 '17 at 14:05

9

You can use unnest() and string_to_array():

 select unnest(string_to_array(t.col, ','))
 from t

answered Mar 11 '17 at 14:05

Gordon Linoff

score 4 · Answer 2 · answered Mar 11 '17 at 14:05

4

You need string_to_array() combined with unnest()

select t.tag
from movies, unnest(string_to_array(tags,'|')) as t(tag)

answered Mar 11 '17 at 14:05

2 Answers2