Postgresql split column into rows

Question

I have 2 columns with a list of towns and I want to split the towns into rows

user 1 | [town1,town2,town3]

I want to split this into rows:

user 1 | town 1
user 1 | town 2
user 1 | town 3

Rahul Tripathi · Accepted Answer · 2016-05-13 11:43:31Z

5

Try this:

SELECT 
    username, 
    regexp_split_to_table(towns, E',') 
FROM yourTable

SQLFIDDLE DEMO

answered May 13, 2016 at 11:43

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

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

Comments

user330315 · Accepted Answer · 2016-05-13 11:42:19Z

1

Assuming the columns are named user_name and towns you can do something like this:

select t1.user_name, t2.town
from the_table t1
  cross join lateral unnest(string_to_array(t1.towns,',')) as t2 (town)

answered May 13, 2016 at 11:42

user330315

Comments

kaisquared · Accepted Answer · 2016-05-13 13:52:10Z

0

Building on a_horse_with_no_name's answer, why not have something simpler like:

--Get some sample data
WITH test(username, town) as
(
select 'user 1', array['town1', 'town2', 'town3']
UNION
select 'user 2', array['town3', 'town2', 'town5']
)

--The following gives the results you are after.
SELECT username, unnest(town)

FROM test

I completely agree with using the unnest function, but I don't understand the reason for the cross join.

OP, it is very important what data type your "list" is stored as. Here I am assuming it is stored as an ARRAY. If it's stored as a string, then Rahul's answer will do the trick.

edited May 13, 2016 at 13:52

answered May 13, 2016 at 13:44

kaisquared

4265 silver badges11 bronze badges

Collectives™ on Stack Overflow

Postgresql split column into rows

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related