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 tables 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). The join selects the
record(s) in B that where live at the same moment as the record in A.
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 allow records in B to match with historical records in A and
thus preventing the insertion of a NULL A record.
Ok in english :-)
If a live record in B does not have a live record in A, it should return
as NULL NULL NULL B.f1 B.f2 B.exptime. But if there are historical
records in A, the join without exptime would match with a historical
record and thus never insert a NULL A record.
A 'left outer join' works fine with the above code, so why not a full
outer join?
Any suggestions or is this a show stopper? Postgres version is 8.1.3.
Thanks,
Harco