Re: Odd 'except' and 'default' interaction behavior

Justin Clift <jclift(at)iprimus(dot)com(dot)au> writes:
> Can you tell me more about this strange behaviour? If it's appropriate,
> I'll try to get it onto the techdocs website.

The underlying bug is that in

INSERT INTO foo SELECT blah blah FROM bar

the INSERT processing tries to realign the SELECT's target list to match
what the INSERTed tuples need to be. This may include reordering the
values, adding NULL or default expressions for missing columns, datatype
coercions, etc.

Unfortunately the INSERT code is too stupid to know that when the SELECT
has a top-level UNION, EXCEPT, or INTERSECT operator, there are multiple
target lists that would all need to be adjusted the same way. It only
changes the first one, leading to funny errors later on.

There are other cases that break too, IIRC, mostly involving GROUP BY
or ORDER BY in the SELECT.

This is all fixed in 7.1 by the simple expedient of using two levels
of target list, so that we don't have to munge the SELECT.

regards, tom lane