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

On Tue, May 22, 2012 at 3:55 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> 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
>
>> 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);
>
> I don't think it's a great idea to make CTEs handle this differently
> from other places where the same issue arises (from memory, views and
> INSERT/SELECT have problems with unknown literals, and there are
> probably other places I'm forgetting).
>
> Should we institute a uniform policy of forcing unknown sub-select
> outputs to text type?  This would almost certainly break a few peoples'
> queries, but the reduction of surprise might be worth it for most.

I think if we can't do real type inference, forcing unknown to text is
probably the least of evils.

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