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

"Andrus" <kobruleht2(at)hot(dot)ee> writes:
> I try to port application to PostgreSQL 8.1+
> The following query runs OK in VFP but causes error in Postgres
> FULL JOIN is only supported with merge-joinable join conditions

> SELECT
> ...
> FROM iandmed
> FULL JOIN koosseis ON iandmed.ametikoht=koosseis.ametikoht
> AND iandmed.Kuluobj= koosseis.objekt1
> AND iandmed.AmetiKoht is not null

Uh, can't you just drop the "iandmed.AmetiKoht is not null" condition?
It seems redundant considering that "iandmed.ametikoht=koosseis.ametikoht"
isn't going to succeed when ametikoht is null.

In the long run we should teach hash join to support full-join behavior,
which would allow cases like this one to work; but it seems not very
high priority, since I've yet to see a real-world case where a
non-merge-joinable full-join condition was really needed. (FULL JOIN
being inherently symmetric, the join condition should usually be
symmetric as well...)

regards, tom lane