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

On Thu, May 3, 2012 at 9:01 PM, <will(at)heroku(dot)com> wrote:
> The following bug has been logged on the website:
>
> Bug reference:      6626
> Logged by:          Will Leinweber
> Email address:      will(at)heroku(dot)com
> PostgreSQL version: 9.1.3
> Operating system:   ubuntu 10.04
> Description:
>
> This was surprising because it worked without the UNION ALL. Casting to text
> fixes the problem. It seems that this should a column of type unknown.
>
> deik3qfhu265n6=> with hello as (select 'hello' as name)
> , bye as (select 'bye' as name)
> select * from hello;
>  name
> -------
>  hello
> (1 row)
>
> deik3qfhu265n6=> with hello as (select 'hello' as name)
> deik3qfhu265n6-> , bye as (select 'bye' as name)
> deik3qfhu265n6-> select * from hello UNION ALL select * from bye;
> ERROR:  failed to find conversion function from unknown to text
>
>
> deik3qfhu265n6=> 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;
>  name
> -------
>  hello
>  bye
> (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);

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