PostgreSQL Arrays – How Does unnest() Treat NULL Values and Empty Arrays?

arrayjoin;postgresqlpostgresql-9.4

I am trying to achieve something like LEFT JOIN with array unnest() function. I want the query to return a row with a null value if the array is empty. Therefore, by using a CASE construct, I wanted to pass fake array with single null element if the source array is empty, but it does not work as expected:

Query 1

select element 
from (
  select array['a']::text[] as arr --< single non-null element
) sub, unnest(
  (
    case when array_length(sub.arr, 1) <= 0 then (array[null])::text[] 
    else sub.arr 
    end
  )
) element
-- returns 1 row with element = "a"

Query 2

select element 
from (
  select array[]::text[] as arr --< empty array
) sub, unnest(
  (
    case when array_length(sub.arr, 1) <= 0 then (array[null])::text[] 
    else sub.arr 
    end
  )
) element
-- returns 0 rows (should return 1 row with element = NULL?)

Query 3

select element 
from (
  select array[null]::text[] as arr --< single null element
) sub, unnest(
  (
    case when array_length(sub.arr, 1) <= 0 then (array[null])::text[] 
    else sub.arr 
    end
  )
) element
-- return single row with element = NULL

Just figured out that select array_length(array[]::text[], 1) returns NULL – my question is why?

Best Answer

To quote Tom Lane in the thread "Should array_length() Return NULL" on pgsql-hackers:

The thing is that that syntax creates an array of zero dimensions, not one that has 1 dimension and zero elements. So "0" would be incorrect.

There is a related TODO item in the Postgres Wiki.

Hard to say whether array_length() should return NULL or 0 for an empty array dimension ...

Solution

You could invert the logic and process the original source array only if the length is >= 1:

WITH tbl(id, arr) AS (
   VALUES
     (1, '{a}'::text[])
   , (2, '{NULL}'::text[])
   , (3, '{}'::text[])
   , (4, '{x,y,z}'::text[])
   )
SELECT id, elem
FROM   tbl t
     , unnest (
          CASE WHEN array_length(t.arr, 1) >= 1
               THEN t.arr
               ELSE '{null}'::text[] END
       ) elem;

Returns at least one row for each source row.

Or, much simpler, use an actual LEFT JOIN (the comma in your FROM clause is short notation for CROSS JOIN):

SELECT id, elem
FROM   tbl t
LEFT   JOIN LATERAL unnest(t.arr) elem ON true;

This fills in NULL values automatically for columns of the right table if no match is found. Exactly what you are trying to achieve.
Related:

db<>fiddle here

Related Question