The following bug has been logged on the website:
Bug reference: 8150
Logged by: Erwin Brandstetter
Email address: brandstetter@falter.at
PostgreSQL version: 9.2.4
Operating system: Multiple
Description: =
PostgreSQL allows to cast the result of unnest() directly. However, I found
inconsistent results with NULL elements in the array. With some array types
NULL element are lost, while they are retained with others:
SELECT unnest('{4,NULL,1}'::int[])::text;
i
---
4
(null)
1
SELECT unnest('{1,NULL,4}'::int[])::int8;
i
---
1
4
The NULL element is lost in the second case, which is unwarranted as far as
I can tell.
Tested with the latest PostgreSQL 9.1.9 and 9.2.4. Identical results.
I presented the case with more details in this question on Stackoverflow:
http://stackoverflow.com/questions/16452501/null-emements-lost-when-casting=
-result-of-unnest
Accompanied by this demo on SQLfiddle:
http://www.sqlfiddle.com/#!12/d41d8/930
As advised by Pavel, I am reporting this as possible bug now.
Regards
Erwin