Thread: Insert/select union bug

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
*/

I need to insert hundreds of thousands of rows, and insert into ...
select union is by far more efficient than multitude of inserts.

Postgres 8.1.3, FreeBSD.

Has this (maybe) been fixed in most recent release?


Peter

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,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

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

On Wed, Sep 27, 2006 at 01:05:56PM +0300, Peter wrote:
> 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.

In general, pushing down of types from the insert is a bit tricky, the
planner tries to unify the UNION first and needs a type for the values.
It guesses "text" if it doesn't know.

Using COPY avoids this issue ofcourse, because there's a direct link to
the table. Similarly, as of 8.2 it will be possible to do:

INSERT INTO table (blah) VALUES (x,y,a),(f,d,g),(s,f,g), etc...

Which will also avoid the issue.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

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.   

In general, pushing down of types from the insert is a bit tricky, the
planner tries to unify the UNION first and needs a type for the values.
It guesses "text" if it doesn't know. 

OK, that explains it.

Using COPY avoids this issue ofcourse, because there's a direct link to
the table. Similarly, as of 8.2 it will be possible to do:

INSERT INTO table (blah) VALUES (x,y,a),(f,d,g),(s,f,g), etc...

Which will also avoid the issue.
 

COPY wont work... my list of columns for insert is also dynamically built and will never cover all fields in table.

Multiple comma-separated values lists will also work... but I dont think I have time to wait for 8.2...

thanks for all the tips!

Peter

On Sep 27, 2006, at 20:39 , Peter wrote:

>> Using COPY avoids this issue ofcourse, because there's a direct
>> link to the table. Similarly, as of 8.2 it will be possible to do:
>> INSERT INTO table (blah) VALUES (x,y,a),(f,d,g),(s,f,g), etc...
>> Which will also avoid the issue.
> COPY wont work... my list of columns for insert is also dynamically
> built and will never cover all fields in table.

How about doing the two-step? Create a temp table with the columns
you're going to load, COPY into the temp table, and do a INSERT INTO
table (...) SELECT ... FROM temp_table?

Michael Glaesemann
grzm seespotcode net

On Wed, Sep 27, 2006 at 02:39:13PM +0300, Peter wrote:
> COPY wont work... my list of columns for insert is also dynamically
> built and will never cover all fields in table.

You don't have to include all columns for copy, just the fields you
copy into, the rest should get the default.

COPY table (fields) FROM STDIN;

The only restriction is that you can't use expressions.

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.