Postgres - split list into rows

Question

I have a table containing lists like so:

['string1','string2']
['string3']

etc

And would like to split each element into its own row.

I've tried converting to an array by doing a regex replace to remove the square brackets :

SELECT
ARRAY[regexp_replace(
          regexp_replace(place_id, '\[', '')
         , '\]', '')::text]
from place_ids

Which gives i.e.:

{'string1','string2'}

However I'm not sure how to split these out. I've tried using unnest :

SELECT
unnest(
    ARRAY[regexp_replace(
              regexp_replace(place_id, '\[', '')
            , '\]', '')::text]
)
from place_ids

But this seems to just remove the array brackets:

'string1','string2'

I was hoping to get something like:

'string1'
'string2'
'string3'

PrasadU · Accepted Answer · 2021-09-22 12:50:11Z

1

from Convert array to rows in Postgres

drop table if exists data ;
drop sequence if exists data_seq;
create table data (
                      rid integer,
                      data varchar
);
create sequence data_seq start with 1;

insert into data (rid, data) VALUES
(1, '[''string1'',''string2'']'),
(2, '[''string3'']')
;

select rid, regexp_split_to_table(regexp_replace(data, '\[|\]', '', 'g'), ',')
from data
;

result:

1,'string1'
1,'string2'
2,'string3'

answered Sep 22, 2021 at 12:50

PrasadU

2,4361 gold badge12 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres - split list into rows

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related