Why does PostgreSQL throw "FULL JOIN is only supported with merge-joinable or hash-joinable join conditions"

Question

Trying to join 2 tables with an OR condition like this:

FULL JOIN table1
     ON (replace(split_part(table1.contract_award_number::text, ' '::text, 2), '-'::text, ''::text)) = table2.contract_award_id 

     OR (btrim(replace(table1.solicitation_number::text, '-'::text, ''::text))) = table2.solicitation_id

But Postgresql is barking at me with:

FULL JOIN is only supported with merge-joinable or hash-joinable join conditions

What gives? For some reason if I add the condition:

WHERE table1.solicitation_number::text ~~ '%%'::text 

the error doesn't occur, but I suspect this is fouling up the FULL JOIN result.

Thanks for any help.

Answer 1

It should be possible to emulate any full outer join between two tables using the following query:

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION ALL
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
WHERE t1.id IS NULL

The first half of the union obtains the records unique to the first table, along with all overlapping records. The second half of the union obtains records particular to the second table only. Applying this pattern to your query gives:

SELECT column1, column2, column3
FROM fpds_opportunities fpds
LEFT JOIN fbo_all_opportunity_detail fbo
    ON replace(split_part(fbo.contract_award_number::text, ' '::text, 2), 
               '-'::text, ''::text) = fpds.contract_award_id OR
       btrim(replace(fbo.solicitation_number::text, '-'::text, ''::text)) = fpds.solicitation_id
UNION ALL
SELECT column1, column2, column3
FROM fpds_opportunities fpds
RIGHT JOIN fbo_all_opportunity_detail fbo
    ON replace(split_part(fbo.contract_award_number::text, ' '::text, 2), 
               '-'::text, ''::text) = fpds.contract_award_id OR
       btrim(replace(fbo.solicitation_number::text, '-'::text, ''::text)) = fpds.solicitation_id
WHERE
    fpds.contract_award_id IS NULL AND fdps.solicitation_id IS NULL;

Answer 2

You could precompute the ugly strings in a subquery (or CTE), and JOIN with that. (this also seems handy for building & testing the query; you never get these string-things right te first time ...)

---

SELECT ...
FROM table2
FULL JOIN (
        SELECT *
        , replace(split_part(table1.contract_award_number::text, ' '::text, 2), '-'::text, ''::text) AS xca
        , btrim(replace(table1.solicitation_number::text, '-'::text, ''::text)) AS xsa
        FROM table1
        ) AS t1
                ON table2.contract_award_id = t1.xca
                OR table2.solicitation_id = t1.xsa
        ;