I’m curious why the following two expressions have a different result — one NULL
and the other TRUE
:
postgres=# select array[1,null]=array[1,null];
?column?
----------
t
(1 row)
postgres=# select (1,null)=(1,null);
?column?
----------
(1 row)
What’s the reasons for this? And in your answer could you please link to the docs in Postgres or some SQL reference where it says the behavior of the row- and list-type when comparing with a NULL
value inside?
2
Answers
Arrays containing
null
in the same position(s) compare equal if all else is equal. Only actualnull
values (including array values as a whole) returnnull
when compared:For row comparison, Postgres follows the SQL standard. The manual on Row and Array Comparisons:
The manual on Row Constructor Comparison:
Bold emphasis mine. So row comparison is subtly different from array comparison. If nested
null
values in row values shall compare equal (like in arrays), useIS NOT DISTINCT FROM
instead of=
:explain first query
select array[1,null]=array[1,null];
3784-3787 compare two array base element data type.
3790-3793 two array dimension/bounds info check.
3796-3813 check weather array base types have equality function. some data type don’t have equality function.
3825-3828 set up iteration for two array.
3838-3853 for each base element in an array, either Datum or null. so iterate and copy datum or null (one element either datum (not null) or null) to variable (it1, isnull1, it2, isnull2).
You answer in line 3845 and line 3848.
src/backend/utils/adt/arrayfuncs.c