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.

Burgess

postgresql.org/message-id/20060313104028.GB6714%40svana.org – Proprietor

It is possible to simulate a full outer join as a union of two joins. – Proprietor

hmm interesting... wouldn't this omit the results that don't join though? – Burgess

If you post your full query I can give you a workaround. – Proprietor

SELECT column1, column2, column3 FROM fpds_opportunities fpds FULL 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 AND (fpds.base_and_all_options_value::numeric > 20000::numeric OR fpds.base_and_all_options_value IS NULL); – Burgess

Sorry, had to cut it down to get it into the StackOverflow character limit – Burgess

The problem with the UNION ALL join method is that it doesn't account for the OR condition in the join (which is what is causing the error in the first place). I'm thinking there might be a way to run 2 joins and union then dedupe? – Burgess

I attempted an answer below. I think we can workaround the ugly join condition because the fpds_opportunities table actually uses direct single columns for the join logic. – Proprietor

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;

Proprietor

in your fist query it should be UNION, not UNION ALL, shouldn't it? Like sql 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 – Longheaded

@Longheaded Yes, since the UNION's built-in distinctness would combine a matching case on both sides into a single record just like the FULL JOIN would, whereas UNION ALL would return both (duplicate) records. BUT, only do this if the data is otherwise unique, or else the UNION's distinctness will collapse more records than you want. You can beat this by including identity columns in the select. – Oleviaolfaction

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
        ;

Caribbean

Answer 3

https://www.postgresql.org/message-id/flat/20060313104028.GB6714%40svana.org https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL_Standard#FULL_OUTER_JOIN_conditions

PostgreSQL currently limits such conditions to ones that are implementable without constructing explicit UNION operations; that is, the condition must be hashable, mergeable, or constant.

Adding support for arbitrary full joins (which would likely require constructing a UNION of the inner join and the two anti-joins) seems like a lot of work for minimal gain.

Vivyanne