Thread

1. UNIONS

   Thomas Swan <tswan@olemiss.edu> — 2000-08-07T16:01:36Z

   Is this a bug or have I just not noticed a nuance with SQL
   
   Assume I have create the two tables
   
   create table foo (
            id int4,
   );
   
   create table foo_child (
            name text
   ) inherits (foo);
   
   If I do
   
   select id, name from foo_child union select id, null as name from foo;
   it works
   
   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
   
   If this isn't a bug, it would be nice to be a nice feature to be able to 
   coax a data type into an 'unknown' field...
   
   I know it would make my life easier... :)
   
   
   - 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."-
   

2. Re: UNIONS

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-08-07T18:07:57Z

   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
   
   

3. Re: UNIONS

   Thomas Swan <tswan@olemiss.edu> — 2000-08-07T18:56:31Z

   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."

4. Re: UNIONS

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-08-07T19:02:13Z

   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