Re: BUG #8696: Type-checking seems to fail on UNIONs with arrays

On 12/23/2013 01:47 AM, jp.deplaix@gmail.com wrote:
> (SELECT NULL AS test)
> UNION
> (SELECT NULL AS test)
> UNION
> (SELECT array_agg(t.name) AS test FROM foo AS t)
> ;
>
>
> fails with:
> ERROR:  UNION types text and text[] cannot be matched
>
>
> Is it (as I suppose) a bug or a well known limitation ?

It's a well known limitation (I knew what was coming before I read it),
but I can't seem to find the right keywords to dig up a reference about
it at the moment.

It has nothing to do with arrays, but the fact that you have *two*
unknown types before a known one.

To wit:

vik=# select null union select null union select 1;
ERROR:  UNION types text and integer cannot be matched
LINE 1: select null union select null union select 1;

The workaround is to provide the type in either the first or second
union-ed query:

vik=# select null::integer union select null union select 1;
int4
----

   1
(2 rows)

vik=# select null union select null::integer union select 1;
?column?
--------

       1
(2 rows)

PS: Interesting that the column name changed with those two queries...

--
Vik