How does unnest() treat NULL values and empty arrays? 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 --&lt; single non-null element
) sub, unnest(
  (
    case when array_length(sub.arr, 1) &lt;= 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 --&lt; empty array
) sub, unnest(
  (
    case when array_length(sub.arr, 1) &lt;= 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 --&lt; single null element
) sub, unnest(
  (
    case when array_length(sub.arr, 1) &lt;= 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?
To quote Tom Lane in the thread &quot;Should array_length() Return NULL&quot; 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 &quot;0&quot; 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 &gt;= 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) &gt;= 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:

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

db&lt;&gt;fiddle here