UNIONS

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:

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.

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. I had a reverse traversal of the classes set
up, but it didn't work because I could allow for all the columns of all the
children.

If you could recommend a place to start, I wouldn't mind looking at the
existing code and seeing what I could do.

-
- Thomas Swan
- Graduate Student - Computer Science
- The University of Mississippi
-
- "People can be categorized into two fundamental
- groups, those that divide people into two groups
- and those that don't."

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

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.

Imho this is expected behavior (maybe even standard). Very easy to
understand.

A more symmetrical
promote-to-common-supertype approach would be nice.

While this sounds sexy it is not what people would expect (imho),
and would have a magic touch to it.

Andreas

Zeugswetter Andreas SB <ZeugswetterA@Wien.Spardat.at> writes:

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.

Imho this is expected behavior (maybe even standard).

Wrong. Read the spec (see 9.3 in SQL99).

regards, tom lane