CODEWITHSUNDEEP
arrayspostgresqlcsv
David Masnou Sánchez
David Masnou Sánchez

Reputation: 99

Transform PSQL rows into arrays?

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!

Upvotes: 1

Views: 93

Answers (2)

Mike Organek
Mike Organek

Reputation: 12494

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, ', ');

Upvotes: 3

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6140

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, ', ')

Upvotes: 1

Related Questions