Postgres - split list into rows-CodePudding

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'

CodePudding user response：

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'