Re: BUG #6626: union all with values of type "unknown"

On Thu, May 3, 2012 at 9:01 PM,  <will@heroku.com> wrote:
> The following bug has been logged on the website:
>
> Bug reference: =A0 =A0 =A06626
> Logged by: =A0 =A0 =A0 =A0 =A0Will Leinweber
> Email address: =A0 =A0 =A0will@heroku.com
> PostgreSQL version: 9.1.3
> Operating system: =A0 ubuntu 10.04
> Description:
>
> This was surprising because it worked without the UNION ALL. Casting to t=
ext
> fixes the problem. It seems that this should a column of type unknown.
>
> deik3qfhu265n6=3D> with hello as (select 'hello' as name)
> , bye as (select 'bye' as name)
> select * from hello;
> =A0name
> -------
> =A0hello
> (1 row)
>
> deik3qfhu265n6=3D> with hello as (select 'hello' as name)
> deik3qfhu265n6-> , bye as (select 'bye' as name)
> deik3qfhu265n6-> select * from hello UNION ALL select * from bye;
> ERROR: =A0failed to find conversion function from unknown to text
>
>
> deik3qfhu265n6=3D> with hello as (select 'hello'::text as name)
> deik3qfhu265n6-> , bye as (select 'bye'::text as name)
> deik3qfhu265n6-> select * from hello UNION ALL select * from bye;
> =A0name
> -------
> =A0hello
> =A0bye
> (2 rows)

I think it should return a column of type text, just as if you'd done this:

select v from (select 'hello' union all select 'bye') x(v);

--=20
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company