Re: sequencing two tables

Shachar Shemesh <psql(at)shemesh(dot)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