Thread: sequencing two tables

Hi list,

I need to create a list of all rows of the combination of two tables.
For reasons I can't go into, I need this list to be in a single query,
and not write a function to do it.

I thought about doing something like this:
select |COALESCE(table1.field, table2.field) as field from table1 full
outer join table2 on false

There is just one small problem. Even though logically this does what
I'm trying to do, postgres says:
"FULL JOIN is only supported with merge-joinable conditions".

I don't even understand the error message, much less whether I'm doing
anything wrong, and whether I'm missing something stupifyingly obvious here.

Help?

Postgres version is 7.4

       Shachar
|

--
Shachar Shemesh
Lingnu Open Source Consulting ltd.
http://www.lingnu.com/

Shachar Shemesh <psqll@shemesh.biz> writes:
> I thought about doing something like this:
> select |COALESCE(table1.field, table2.field) as field from table1 full
> outer join table2 on false

Perhaps you are looking for UNION ALL?  If not, I'm not sure what you
expect this query to mean.

            regards, tom lane

On Sun, 28 Nov 2004, Shachar Shemesh wrote:

> I need to create a list of all rows of the combination of two tables.
> For reasons I can't go into, I need this list to be in a single query,
> and not write a function to do it.
>
> I thought about doing something like this:
> select |COALESCE(table1.field, table2.field) as field from table1 full
> outer join table2 on false
>
> There is just one small problem. Even though logically this does what
> I'm trying to do, postgres says:
> "FULL JOIN is only supported with merge-joinable conditions".
>
> I don't even understand the error message, much less whether I'm doing
> anything wrong, and whether I'm missing something stupifyingly obvious here.

We don't support join conditions like the above at this point.  The
message isn't terribly helpful though.

Fortunately, I think some variant like
 select table1.field from table1
  union all
 select table2.field from table2;
will give you the output you're looking for.

Stephan Szabo wrote:

>We don't support join conditions like the above at this point.  The
>message isn't terribly helpful though.
>
>
Purely out of interest, what does that message mean? Why aren't they
supported?

>Fortunately, I think some variant like
> select table1.field from table1
>  union all
> select table2.field from table2;
>will give you the output you're looking for.
>
>
It's exactly what I'm looking for. Thank you and Tom.

       Shachar

--
Shachar Shemesh
Lingnu Open Source Consulting ltd.
http://www.lingnu.com/

Shachar Shemesh <psql@shemesh.biz> writes:

> Stephan Szabo wrote:
>
>> We don't support join conditions like the above at this point.  The
>> message isn't terribly helpful though.
>
> Purely out of interest, what does that message mean? Why aren't they supported?

I'll take a swing at this.

The database needs to implement the join using some kind of method. "merge
join" is just one particular method in which the two sides are sorted
according to some key that's being compared and then walked down in parallel
looking for matches. This only works if the clause is an equality of some kind
between columns that the database can sort on.

There are other methods for doing joins, but this is the only method available
for postgres for handling full outer joins.

Most of the other methods (nested loops, hash joins, etc) just wouldn't work
well for full outer joins since they don't involve looking at every record
from both tables -- in fact most of the reason they're useful is precisely
because they don't need to do so.

The only other option would be to check every possible combination of records
from each table. Besides being incredibly slow, this would still leave you
with enormous practical problems trying to keep track of which records had
been or hadn't been matched.

> >Fortunately, I think some variant like
> > select table1.field from table1
> >  union all
> > select table2.field from table2;
> >will give you the output you're looking for.
> >
> It's exactly what I'm looking for. Thank you and Tom.

Note that it's the "ALL" that saves you. If you did a straight "UNION" then
the database still has to eliminate duplicates. In that case it's back to
solving the same problems above. And in fact Postgres will fail if the fields
being unioned aren't something it can sort:

 db=# select point '(0,1)' union select point '(1,0)';
 ERROR:  could not identify an ordering operator for type point
 HINT:  Use an explicit ordering operator or modify the query.

This is a different code path but it's running into basically the same
problem.

--
greg