On Mon, Mar 13, 2006 at 11:02:35AM +0100, Harco de Hilster wrote: > 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 Ouch! You've got yourself a nasty one. The technical reason why this doesn't work is because full outer joins in postgres are implemented by a merge join. Basically sort the two tables and then compare them record by record, emitting null columns when necessary. However, this only works when the condition is merge-joinable, usually on ordered datatypes with an equality operator. I think the reason it hasn't been done for general join conditions is because we havn't thought of an efficient algorithm. Basically, for each row in the left table you need to find all matching rows in the right table. Afterwards you have to find all the rows in the right table which havn't been used. i.e. you can emulate it by doing a: SELECT * FROM A LEFT JOIN B ON (X) UNION ALL SELECT * FROM A RIGHT JOIN B ON (X) WHERE A.id IS NULL; But this is double joining and so not terribly efficient. And if the tables were subqueries it would be worse and quite possibly wrong if the output isn't constant. However, I wonder if youre case couldn't be handled with a time-interval datatype such that you condition is merge-joinable on that type. I can't quite see it though... > 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))) Hope this helps, -- Martijn van Oosterhout <kleptog@xxxxxxxxx> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment:
signature.asc
Description: Digital signature
