How to split array into rows in Postgresql

Question

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!

I'm confused. I thought Redshift didn't support arrays: docs.aws.amazon.com/redshift/latest/dg/…. — Gordon Linoff
– Gordon Linoff, Commented Apr 23, 2017 at 15:31
You are right @GordonLinoff in this case it is only PostgreSQL 8.0.2 what I am using. Just edited that out. — Henry
– Henry, Commented Apr 23, 2017 at 15:34

Gordon Linoff · Accepted Answer · 2017-04-23 16:14:04Z

24

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;

edited Apr 23, 2017 at 16:14

answered Apr 23, 2017 at 15:43

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

2 Comments

Henry Over a year ago

Thank 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 Over a year ago

I 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!

Collectives™ on Stack Overflow

How to split array into rows in Postgresql

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related