Re: Insert/select union bug

[Peter <peter(at)greatnowhere(dot)com>]

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