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

Re: UNION with more than 2 branches

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Peter Eisentraut <peter_e(at)gmx(dot)net>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: UNION with more than 2 branches
Date: 2007-04-24 17:17:46
Message-ID: 5404.1177435066@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> The resolution to my problem with the select_common_type() error message 
> turned out to be that this doesn't work:

> postgres=# select null union select null union select 1;
> ERROR:  UNION types text and integer cannot be matched

Yeah, this has been noted before.  The sticking point is that it's not
clear that resolving types across more than two branches at a time is
legal per SQL spec.  The spec defines UNION as exactly two at a time,
ie the above is really
	(select null union select null) union select 1;
and there is not any language that would justify allowing the "1" to
determine the data type of the inner UNION.  It would not be all that
important in a UNION ALL case, maybe, but for UNION the assigned data
type determines what values are considered duplicates, and thus can
have real impact on the results.

Maybe we should just ignore those qualms and do it anyway --- I must
admit that I'm hard-pressed to come up with a situation where anyone
would really want different datatypes used in the inner union than
the outer.

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Gregory StarkDate: 2007-04-24 17:34:20
Subject: Re: UNION with more than 2 branches
Previous:From: Peter EisentrautDate: 2007-04-24 16:57:14
Subject: Re: Google SoC: column-level privilege subsystem

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