2

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?

4

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:

SQL Fiddle.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.