Skip site navigation (1) Skip section navigation (2)

BUG #5974: UNION construct type cast gives poor error message

From: "Jeff Wu" <jwu(at)atlassian(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #5974: UNION construct type cast gives poor error message
Date: 2011-04-12 20:18:47
Message-ID: 201104122018.p3CKIlWR042915@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      5974
Logged by:          Jeff Wu
Email address:      jwu(at)atlassian(dot)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

Responses

pgsql-bugs by date

Next:From: Merlin MoncureDate: 2011-04-12 22:13:01
Subject: Re: [GENERAL] PostgreSQL backend process high memory usage issue
Previous:From: Kevin GrittnerDate: 2011-04-12 19:16:02
Subject: Re: Missing documentation for error code: 80S01

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group