Split value in single row to multiple rows with Postgresql

Question

With Postgresql I have the query:

select '1' , '2', '3';

with result

I would like to have next result:

column
1
2
3

How can I do that?

Frank Heikens · Accepted Answer · 2021-04-13 07:50:15Z

2

You could create an array and then use UNNEST():

SELECT UNNEST(ARRAY[1,2,3]);

answered Apr 13, 2021 at 7:50

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

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

Comments

D-Shih · Accepted Answer · 2021-04-13 07:48:22Z

1

You are looking for Unpivot, then you can try to use UNION ALL

select '1' column
UNION ALL
SELECT '2'
UNION ALL
SELECT '3'

or you can try to use JOIN LATERAL

SELECT s.*
FROM test t
JOIN LATERAL (VALUES(t.a),(t.b),(t.c)) s(column) ON TRUE

sqlfiddle

answered Apr 13, 2021 at 7:48

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Comments

Laurenz Albe · Accepted Answer · 2021-04-13 10:11:03Z

1

I prefer to use a table function for that:

SELECT CAST(i AS text) FROM generate_series(1, 3) AS i;

edited Apr 13, 2021 at 10:11

answered Apr 13, 2021 at 8:35

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

7 Comments

davidleongz Over a year ago

It doesn't work with: SELECT i FROM ('1','2') AS i;

Laurenz Albe Over a year ago

Oh, you want strings. Fixed.

davidleongz Over a year ago

SELECT CAST(i AS text) FROM ('1', '2') AS i; RESULT -> ERROR: syntax error at or near "1"

user330315 Over a year ago

@DavidLeon: please read the answer: it uses from generate_series(...) not from ('1', '2')

davidleongz Over a year ago

@a_horse_with_no_name the question is with String

|

Collectives™ on Stack Overflow

Split value in single row to multiple rows with Postgresql

3 Answers 3

Comments

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related