In our fine manual, at http://www.postgresql.org/docs/devel/static/typeconv-union-case.html it's claimed that the nontrivial parts of UNION type resolution work like this:
4. Choose the first non-unknown input type which is a preferred type in that category, if there is one. 5. Otherwise, choose the last non-unknown input type that allows all the preceding non-unknown inputs to be implicitly converted to it. (There always is such a type, since at least the first type in the list must satisfy this condition.) This appears to have only the vaguest of resemblances to what select_common_type() actually does, which is to make a single pass over the inputs in which it does this: /* * take new type if can coerce to it implicitly but not the * other way; but if we have a preferred type, stay on it. */ Thus for example there's a surprising inconsistency between these cases: regression=# select pg_typeof(t) from (select 'a'::text union select 'b'::char(1)) s(t); pg_typeof ----------- text text (2 rows) regression=# select pg_typeof(t) from (select 'a'::char(1) union select 'b'::text) s(t); pg_typeof ----------- character character (2 rows) I think that at the very least, we ought to prefer preferred types, the way the manual claims. I'm less certain about whether step 5 is ideal as written. This came up because some of my Salesforce colleagues were griping about the fact that UNION isn't commutative. They argue that the type resolution behavior ought not be sensitive at all to the ordering of the inputs. I'm not sure we can achieve that in general, but the current approach certainly seems more order-sensitive than it oughta be. Some trolling in the git history says that the last actual change in this area was in my commit b26dfb95222fddd25322bdddf3a5a58d3392d8b1 of 2002-09-18, though it appears the documentation has been rewritten more recently. It's a bit scary to be proposing to change behavior that's been stable for eleven years, but ... Thoughts? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers