How To Split Array Into Rows In Postgresql

When running this query: SELECT id,selected_placements FROM app_data.content_cards I get a table like this: +----+-------------------------------+ | id | selected_placements

Solution 1:

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 ISNOTNULL;

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 (
     select1as 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 CROSSJOIN
           (SELECTMAX(ARRAY_UPPER(cc.selected_placements, 1)) as maxup
            FROM content_cards cc
           ) ccup
     ) x
WHERE selected_placements[num]  ISNOTNULL;