postgresql - 为什么 PostgreSQL 抛出 "FULL JOIN is only supported with merge-joinable or hash-joinable join conditions"

Question

尝试用这样的 OR 条件连接 2 个表:

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

但是 Postgresql 向我咆哮:

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

什么给了？出于某种原因，如果我添加条件:

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

错误没有发生，但我怀疑这会破坏 FULL JOIN 结果。

感谢您的帮助。

Answer 1

应该可以使用以下查询模拟两个表之间的任何完全外部连接:

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

并集的前半部分获取第一个表的唯一记录，以及所有重叠的记录。 union 的后半部分只获取第二个表的特定记录。将此模式应用于您的查询会得到:

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;