postgres, null array vs 0-length array: they both returns is null = true, but behave differently

Question

Inside functions (plpgsql) and code blocks i tested that pgsql really doesn't need that arrays are initialized.
Try this: I just test array length before and after having appended an element.

do $$ 
declare ai int[];
begin

--BEFORE
raise notice 'ai length: %', array_length(ai,1);

--append the element (to the NON initialized array)
ai = array_append(ai,'2016');

--AFTER
raise notice 'ai length: %', array_length(ai,1);

end;
$$

No errors, and the result is:

NOTICE:  ai length: <NULL>
NOTICE:  ai length: 1

You can even access an out-of-bounds element wihtout exception thrown:

begin
raise notice 'ai length: %', array_length(ai,1);
raise notice 'element 99: %', ai[99];

Output:

NOTICE: ai length: NULL
NOTICE: element 99: NULL

So you start to think that arrays are somehow lazy-initialized, just use them. But that's not always the case, consider this code:

do $$  
  declare ai int[]; 
  declare i int; 

begin

foreach i in array ai loop
    raise notice 'Element: %', i;
end loop;

end; $$

This does throw an error! The following:

ERROR: FOREACH expression must not be null

So, in some cases arrays have to be initialized. Let's check, and let's discover what the difference is between initialized and not:

do $$
declare ai int[];
declare x int;
declare i int;
begin

raise notice 'Array length: %', array_length(ai,1);

ai = array[] :: int[];

raise notice 'Array length: %', array_length(ai,1);

foreach i in array ai loop
    raise notice 'Element: %', i;
end loop;

end;
$$

Output:

NOTICE: Array length: NULL
NOTICE: Array length: NULL

So: the line

ai = array[] :: int[];

works, infact the for loop doesn't throw the exception any more.

But, after initialization,

raise notice 'Array length: %', array_length(ai,1);

still gives 'null', an that is very strange.

The questions are: is it correct the way i initialize the array?
Is there an explanation for this strange behavior?

(Postgres Version: 9.5)

klin klin · Accepted Answer · 2016-08-25T21:59:04

In plpgsql functions I usually initialize arrays in declare:

declare
    arr int[] = '{}';

A non-initialized array is null of course.

To distinguish null array from empty one you can use cardinality() instead of array_length():

with data(ar) as (
    values (array[1]), (array[]::int[]), (null)
)
select ar "array", ar isnull "isnull?", array_length(ar, 1), cardinality(ar)
from data;

 array | isnull? | array_length | cardinality 
-------+---------+--------------+-------------
 {1}   | f       |            1 |           1
 {}    | f       |              |           0
       | t       |              |            
(3 rows)

postgres, null array vs 0-length array: they both returns is null = true, but behave differently

1 Answers