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