How to split array into rows in Postgresql
15,923
I would suggest that you upgrade your version of Postgres. All supported versions support unnest()
:
SELECT x.*
FROM (SELECT id, UNNEST(selected_placements) as selected_placement
FROM app_data.content_cards
) x
WHERE selected_placement IS NOT NULL;
In earlier versions, you can strive to pick them out one at a time. The following is tested and works, albeit in 9.5:
with content_cards as (
select 1 as id, array['a', 'b', 'c'] as selected_placements
)
SELECT id, selected_placements[num] as selected_placement
FROM (SELECT cc.*, generate_series(1, ccup.maxup) as num
FROM content_cards cc CROSS JOIN
(SELECT MAX(ARRAY_UPPER(cc.selected_placements, 1)) as maxup
FROM content_cards cc
) ccup
) x
WHERE selected_placements[num] IS NOT NULL;
Author by
Henry
Updated on June 14, 2022Comments
-
Henry almost 2 years
When running this query:
SELECT id,selected_placements FROM app_data.content_cards
I get a table like this:
+----+-------------------------------+ | id | selected_placements | +----+-------------------------------+ | 90 | {162,108,156,80,163,155,NULL} | +----+-------------------------------+ | 91 | {} | +----+-------------------------------+
What I want to do now is get this same information but with the arrays splitted into rows so I get a result like this:
+----+---------------------+ | id | selected_placements | +----+---------------------+ | 90 | 162 | +----+---------------------+ | 90 | 108 | +----+---------------------+ | 90 | 156 | +----+---------------------+ | 90 | 80 | +----+---------------------+ | 90 | 163 | +----+---------------------+ | 90 | 155 | +----+---------------------+
As you can see I don't want to get rows with null value in "selected_placements".
I am using PostgreSQL 8.0.2.
Many thanks!
-
Henry about 7 yearsThank you for this quick answer. I get this error when running the query screencast.com/t/uBL3cGwt To give you some background, when I check the database version we use, this is what I get: version PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.1232. From what I understand, I am reading all the data from Redshift (using SQLWorkbenchJ), but actually it is coming from a Postgresql db that we have separately. The synchronization between the two dbs is done with stitchdata.com. Thank you very much!
-
Henry about 7 yearsI already checked in a separate environment where I have PostgreSQL 9.4.7. and the unnest () answer works perfect. I am upvoting the answer already but it would be great if I could get a solution for the other error I am having. Thank you!