0

I have a table in postgresql 14 like this one:

text                classes
some string         [food, drink]
another string      [food, medicine, drink]
another random      [car]

And I want to get this as output:

text                class_1   class_2  class_3
some string         food       drink      
another string      food     medicine   drink
another random      car

So I want to strip the [] off and explode each of the string into columns.

I am trying:

select text, replace(replace(unnest(string_to_array(classes, ',')), '[', ' '),']','') from tbl

but i am getting each of the classes in one line which duplicates the text columns

Also, is there any clean way to remove the []?

math_guy_shy
  • 161
  • 6
  • Sigh. Why do people "invent" so many new formats of storing lists when there are perfectly fine supported solutions like JSON, or proper arrays. But storing multiple values in a single column is usually a bad idea to begin with. –  Jun 15 '22 at 04:56

1 Answers1

0

You can use translate to get rid of the square brackets. Then use split_part to get one column for each element:

select "text", 
       split_part(translate(classes, '[]', ''), ',', 1) as class_1,
       split_part(translate(classes, '[]', ''), ',', 2) as class_2,
       split_part(translate(classes, '[]', ''), ',', 3) as class_3
from the_table;

It is not possible to make this dynamic. A fundamental restriction of the the SQL language is, that the number, names and data types of all result columns must be known before the database starts retrieving data.