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