Unpivoting two columns in PostgreSQL

We recently had a need to unpivot two columns with item id’s in them so they’d come out as a single column with distinct values. Some of the entries had <null> as the parent id, and null should not appear in the output.

One could solve this with a simple UNION, but the view we were using was unfortunately rather expensive to run, so iterating it twice for the UNION was not an option.

So here goes:

SELECT DISTINCT
   UNNEST(
        CASE
           WHEN parent_item_id IS NULL THEN array[item_id]
           ELSE array[item_id, parent_item_id]
        END
   ) AS item_id
FROM rather_expensive_view
WHERE
   owner_id = 123456

In short, UNNEST unpivots arrays to rows, and we avoid printing NULL as an item_id when parent_item_id is NULL by not including it in the array in the CASE statement. The result is one iteration of the view, and a pretty list of item_ids.

To visualize the operation:

We go from:
---
item_id item_parent_id
10 NULL
11 4
12 3
---

To:
---
item_id
3
4
10
11
12
---

No NULL in the output :D

Adding additional columns to print, along with the UNNEST is fine. The additional columns will be duplicated for both rows in the case where an item has a parent. The output can, of course, also be joined with something like a sales table, so find sales of each item, including the parents.

Leave a Reply

Your email address will not be published.