PostgreSQL unnest with empty array

Question

I use postgreSQL 9.1. In my database there is a table which looks like

id | ... | values
-----------------------
1  | ... | {1,2,3}
2  | ... | {}

where id is an integer and values is an integer array. The arrays can be empty.

I need to unnest this list. If I query

select id, ..., unnest(values)
from table

I get three rows for id = 1 (as expected) and no lines for id = 2. Is there a way to get a result like

id  | ... | unnest
-------------------
1   | ... | 1
1   | ... | 2
1   | ... | 3
2   | ... | null

i.e. a query which also contains the lines which have an empty array?

Answer 1

select id, 
       case 
         when int_values is null or array_length(int_values,1) is null then null
         else unnest(int_values)
       end as value
from the_table;

(note that I renamed the column values to int_values as values is a reserved word and should not be used as a column name).

SQLFiddle: http://sqlfiddle.com/#!1/a0bb4/1

---

Postgres 10 does not allow to use unnest() like that any more.

You need to use a lateral join:

select id, t.i
from the_table
   cross join lateral unnest(coalesce(nullif(int_values,'{}'),array[null::int])) as t(i);

Online example: http://rextester.com/ALNX23313

---

It can be simplified even further when using a left join instead of the cross join:

select id, t.i
from the_table
 left join lateral unnest(int_values) as t(i) on true;

Online example: http://rextester.com/VBO52351

Answer 2

This works on Postgres 10 also:

SELECT id, UNNEST(CASE WHEN "values" <> '{}' THEN "values" ELSE '{null}' END)

Answer 3

Original answer

This works up to Postgres 9.6. The behavior of set-returning functions in the SELECT list was changed (sanitized) in Postgres 10. See below!

Upon revisiting this question it struck me that this can be simpler and faster.
Reverse the logic of the currently accepted solution by @a_horse:

SELECT id, CASE WHEN values <> '{}' THEN unnest(values) END AS value
FROM   tbl;

fiddle

This returns a row with NULL in value for an empty array as well as for a NULL array, because only an array with elements in it produces TRUE in the test values <> '{}'.

Works for arrays of any type, since the literal '{}' is automatically coerced to a matching type.

Without explicit ELSE branch, CASE returns NULL, which is what we want anyway.

Arrays with a NULL elements will return rows regardless.

However. I found an anomaly and posted a question concerning that:

• NULL emements lost when casting result of unnest()

Turned out to be a bug that was fixed after my report for pg 9.3+.

Since Postgres 10

The short syntax above stops working after the behavior of set-returning functions in the SELECT list has been reworked (sanitized). See:

• What is the expected behaviour for multiple set-returning functions in SELECT clause?

A LATERAL subquery is the clean, versatile solution, and works since Postgres 9.3, where LATERAL was added:

SELECT t.id, v.value
FROM   tbl t
LEFT   JOIN LATERAL unnest(t.values) AS v(value) ON true;

Or, with minimal syntax:

SELECT id, value
FROM   tbl
LEFT   JOIN LATERAL unnest(values) value ON true;

fiddle

See:

• What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?

Answer 4

select id,
    unnest (
        "values"
        ||
        (array[null]::integer[])[1:(array_upper("values", 1) is null)::integer]
    )
from "table"

Answer 5

You will need to use self LEFT JOIN, like this (also on SQL Fiddle):

SELECT t.id, u.u
  FROM tab t
  LEFT JOIN (SELECT id, unnest(vals) u FROM tab) u
    USING (id);

Note, that for bigger tables query will be performing badly.