CODEWITHSUNDEEP
sqlpostgresql
davidleongz
davidleongz

Reputation: 181

Split value in single row to multiple rows with Postgresql

With Postgresql I have the query:

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

with result

enter image description here

I would like to have next result:

column
1
2
3

How can I do that?

Upvotes: 0

Views: 861

Answers (3)

Laurenz Albe
Laurenz Albe

Reputation: 246288

I prefer to use a table function for that:

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

Upvotes: 1

Frank Heikens
Frank Heikens

Reputation: 126991

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

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

Upvotes: 2

D-Shih
D-Shih

Reputation: 46219

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

Upvotes: 1

Related Questions