Re: Cannot do time-aligned listing with FULL JOIN, get ERROR: FULL JOIN is only supported with merge-joinable join conditions - Mailing list pgsql-novice

"John Koerber" <johnk@musicreports.com> writes:
> SELECT *
>   FROM public.events_a A
>   FULL JOIN public.events_b B
>     ON (start_a, end_a) OVERLAPS (start_b, end_b)
>  ORDER BY coalesce(start_a, start_b), start_b  ;
>  [ doesn't work ]

> In practice, is there:
> * A different structuring the time spans of the 'events' such that a
> merge-joinable condition can be found?
> * A possibility of teaching Hash Join to do the FULL JOIN on
> non-merge-joinable conditions?

Even if we could do full joins by hashing, that wouldn't help you since
OVERLAPS is no more hashable than it is mergeable.  The only possible
join plan would be nestloop, with a work table the size of the inner
input to keep track of which inner rows hadn't been joined to anything
:-(

            regards, tom lane