The following bug has been logged online:
Bug reference: 5974
Logged by: Jeff Wu
Email address: jwu@atlassian.com
PostgreSQL version: 9.0
Operating system: Mac OS X
Description: UNION construct type cast gives poor error message
Details:
The UNION construct (as noted on this page:
http://www.postgresql.org/docs/9.0/static/typeconv-union-case.html) will
cast unknown types to TEXT, however, if you try to do three or more UNIONs
the order in which the UNIONs are executed will cause some columns to be
cast to TEXT prematurely. The result is a type mismatch error.
For example:
SELECT 1,null,null
UNION
SELECT 2,3,null
UNION
SELECT 3,null,4
will fail while
SELECT 1,null,null::INTEGER
UNION
SELECT 2,3,null
UNION
SELECT 3,null,4
will succeed.
This is not a critical error, but I would say that the error message is
misleading because it is not obvious that Postgres casts unknown columns to
TEXT automatically.
The current error message is:
ERROR: UNION types text and integer cannot be matched
I would suggest something like:
ERROR: UNION types text and integer cannot be matched. HINT: Postgres casts
unknown types to TEXT by default.
Thanks,
Jeff