Обсуждение: UNIONS

<font face="Arial, Helvetica">Is this a bug or have I just not noticed a nuance with SQL<br /><br /> Assume I have
createthe two tables <br /><br /></font><font face="Courier, Courier"><b>create table foo (<br />         id int4,<br
/>);<br /><br /> create table foo_child (<br />          name text<br /> ) inherits (foo);<br /><br /></b></font><font
face="Arial,Helvetica">If I do <br /><br /></font><font face="Courier, Courier"><b>select id, name from foo_child union
selectid, null as name from foo;<br /></b></font><font face="Arial, Helvetica">it works<br /><br /></font><font
face="Courier,Courier"><b>select id, null as text from foo union select id, name from foo_child;<br /></b></font><font
face="Arial,Helvetica">fails with <br /><br /></font><font face="Courier, Courier"><b>unable to trasform {insert
whatevertype here} into unknown<br />         Each UNION | EXCEPT | INTERSECT clause must have compatible target
types<br/><br /></b></font><font face="Arial, Helvetica">If this isn't a bug, it would be nice to be a nice feature to
beable to coax a data type into an 'unknown' field... <br /><br /> I know it would make my life easier... :)<br /><br
/><br/> - <b><u>Thomas Swan</u></b>                                   <br /> - Graduate Student  - Computer Science<br
/>- The University of Mississippi<br /> - <br /> - "People can be categorized into two fundamental <br /> - groups,
thosethat divide people into two groups <br /> - and those that don't."- <br /></font> 

Thomas Swan <tswan@olemiss.edu> writes:
> select id, null as text from foo union select id, name from foo_child;
> fails with
> unable to trasform {insert whatever type here} into unknown
>          Each UNION | EXCEPT | INTERSECT clause must have compatible target 
> types

The UNION type-resolution code could use some work; right now I think
the algorithm is to use the types of the first SELECT and force
everything else into that.  A more symmetrical
promote-to-common-supertype approach would be nice.  The UNION code is
such a mess that I haven't wanted to touch it until we do querytree
revisions in 7.2, though.

In the meantime, you should force the NULL to have the datatype you want
with something like "null::text" or "cast (null as text)".  Note that
the way you have it above is only assigning a column label that happens
to be "text"; it's not a type coercion.
        regards, tom lane

At 01:07 PM 8/7/2000, Tom Lane wrote:<br /><blockquote cite="cite" type="cite">Thomas Swan <tswan@olemiss.edu>
writes:<br/> > select id, null as text from foo union select id, name from foo_child;<br /> > fails with<br />
>unable to trasform {insert whatever type here} into unknown<br /> >          Each UNION | EXCEPT | INTERSECT
clausemust have compatible target <br /> > types<br /><br /> The UNION type-resolution code could use some work;
rightnow I think<br /> the algorithm is to use the types of the first SELECT and force<br /> everything else into
that. A more symmetrical<br /> promote-to-common-supertype approach would be nice.  The UNION code is<br /> such a mess
thatI haven't wanted to touch it until we do querytree<br /> revisions in 7.2, though.<br /><br /> In the meantime, you
shouldforce the NULL to have the datatype you want<br /> with something like "null::text" or "cast (null as text)". 
Notethat<br /> the way you have it above is only assigning a column label that happens<br /> to be "text"; it's not a
typecoercion.<br /></blockquote><font face="Microsoft Sans Serif"><br /> The reason I was asking is that I had an idea
fordoing the select ** from tablename* that would expand.<br /><br /> It could be macro of sorts but part of it
dependingon creating a null table or the equivalent of it with nothing but a null column for each different column of
theset.  I had  a reverse traversal of the classes set up, but it didn't work because I could allow for all the columns
ofall the children.   <br /><br /> If you could recommend a place to start, I wouldn't mind looking at the existing
codeand seeing what I could do.<br /><br />  - <br /> - <b><u>Thomas Swan</u></b>                                   <br
/>- Graduate Student  - Computer Science<br /> - The University of Mississippi<br /> - <br /> - "People can be
categorizedinto two fundamental <br /> - groups, those that divide people into two groups <br /> - and those that
don't."</font>

Thomas Swan <tswan@olemiss.edu> writes:
> The reason I was asking is that I had an idea for doing the select ** from 
> tablename* that would expand.

> It could be macro of sorts but part of it depending on creating a null 
> table or the equivalent of it with nothing but a null column for each 
> different column of the set.

What happens when two different child tables have similarly-named
columns of different types?

In any case, this wouldn't be a very satisfactory solution because you
couldn't tell the difference between a null stored in a child table and
the lack of any column at all.  We really need to do it the hard way,
ie, issue a new tuple descriptor as we pass into each new child table.

There appears to have once been support for that back in the Berkeley
days; you might care to dig through Postgres 4.2 or so to see how they
did it.
        regards, tom lane