ERROR: FULL JOIN is only supported with merge-joinable join conditions

Hi all,

I am porting my application from Ingres to Postgres, and I have the
following problem. I am not sure if this is a known limitation of
Postgresql or a bug. My code works under Ingres but fails in Postgres
with the following error:

ERROR:  FULL JOIN is only supported with merge-joinable join conditions

My table contain temporal data e.g.

Table A:
f1 | f2 | modtime | exptime
--------------------------
A | B | t0 | t2   <= historical record
A | C | t2 | t6    <= historical record
A | D | t6 | NULL   <= live record

Table B:
f1 | f2 | modtime | exptime
--------------------------
F | G | t1 | t3 <= historical record
F | H | t3 | t5 <= historical record
F | I | t5 | NULL <= live record

All queries on live data are of the form: select * from a where f1 = xx
and exptime is NULL

A full outer join on two tables with temporal data looks like this:

select *
from A
full outer join B on A.f1 = B.f1  and  ((A.ExpTime IS NULL AND B.ExpTime
IS NULL) OR (A.ModTime <= B.ExpTime AND (B.ExpTime > A.ExpTime OR
B.ExpTime IS NULL)))

The primary keys of A and B are (f1, exptime).


Postgres's problem is with the <=, > and is null conditions in the full
outer join. These are probably not 'merge-joinable', so the query fails.
Shouldn't it try a different method instead of failing??

I cannot move the conditions on exptime to the where clause, because
that would introduce (outer join) extra records with historical data in
B that are not in the lifetime span of records in A.

Any suggestions or is this a show stopper?

Thanks,

Harco