Re: Insert/select union bug

Martijn van Oosterhout wrote:
> On Wed, Sep 27, 2006 at 12:14:44PM +0300, Peter wrote:
>
>> create table temp(a timestamptz);
>>
>> insert into temp(a) select NULL; /* this passes */
>>
>> insert into temp(a) select NULL union select NULL; /* fails:
>> ERROR: column "a" is of type timestamp with time zone but expression is
>> of type text
>> */
>>
>
> Perhaps you could indicate in the subselects the type? For example:
>
> insert into temp(a) select NULL::timestamptz union select NULL;
>
> I think as long as the first has the right type, you're set.
>
> BTW, UNION ALL is probably more efficient.
>
> Have a nice day,
>

UNION ALL would probably be quicker still, you're right.

Typecast eliminates the issue, you're right on that as well. However, my
problem is that those statements are dynamically generated on various
tables/columns, so typecasting would mean extracting target field type
and translating fieldtype code into SQL typename. Rather messy.

Peter