I have a postgresql database containing a table that looks something like this simplified example:
Name Pets Hobbies
Jhon Dog, Cat, Cat Music
Jane Dog Sports, Music
Dan Dog, Turtle Reading, Sports, VideoGames
Lia Cat, Cat VideoGames, Sports
As you can see, in this example I have 3 columns, 2 of them containg some strings separated by commas. What I would like to do is to get that values into arrays, so if I do something like:
SELECT
Name,
Pets,
Hobbies
FROM
examples_table;
I get an output like this (having the contents of the rows with multiple values in arrays):
Name Pets Hobbies
Jhon {Dog, Cat, Cat} {Music}
Jane {Dog} {Sports, Music}
Dan {Dog, Turtle} {Reading, Sports, VideoGames}
Lia {Cat, Cat} {VideoGames, Sports}
So that way I can access to the contents I want easily (for example, if I want to access who has Cat as Pets). I know this is a possible thing to do on PostgreSQL, but i don't know how to do it (also I'm currently working on psql version 12 on an Ubuntu device). Thanks!
The built-in string_to_array() will handle this for you:
select name,
string_to_array(pets, ', ') as pets,
string_to_array(hobbies, ', ') as hobbies
from examples_table;
If you want to change the table in-place:
alter table examples_table
alter column pets type text[]
using string_to_array(pets, ', '),
alter column hobbies type text[]
using string_to_array(hobbies, ', ');
Try this:
select
name,
regexp_split_to_array(pets,', '),
regexp_split_to_array(hobbies,', ') from example
or
select
name,
string_to_array(pets,', '),
string_to_array(hobbies,', ')
from example
EDIT for your comment's requirement run this:
update example set
pets=regexp_split_to_array(pets, ', ') ,
hobbies=regexp_split_to_array(hobbies, ', ')