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