Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

like image 289
Henry Avatar asked Apr 23 '17 15:04

Henry


People also ask

How do you split an array into two?

To divide an array into two, we need at least three array variables. We shall take an array with continuous numbers and then shall store the values of it into two different variables based on even and odd values.

What is [] in PostgreSQL?

We access array elements using the subscript within square brackets [] . By default, PostgreSQL uses one-based numbering for array elements. It means the first array element starts with number 1.

How do I split a column in PostgreSQL?

We can use any of the string to split it; we can also use a column name as a substring to split the data from the column. Delimiter argument is used to split the string into sub-parts by using a split_part function in PostgreSQL. We can split the string into a number of parts using delimiter.


1 Answers

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;
like image 183
Gordon Linoff Avatar answered Sep 28 '22 10:09

Gordon Linoff