[GENERAL] FULL JOIN is only supported with merge-joinable join conditions

[Andrus]

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

How to fix ?

Andrus.


SELECT
 ametikoh.Nimetus as ametikoht,
 Isik.nimi,
 Isik.eesnimi,
 koosseis.kogus,
 COALESCE( iandmed.Kuluobj, koosseis.objekt1) as osakond

FROM iandmed
 FULL JOIN koosseis ON iandmed.ametikoht=koosseis.ametikoht
   AND  iandmed.Kuluobj= koosseis.objekt1
   AND iandmed.AmetiKoht is not null
 JOIN ametikoh ON COALESCE(iandmed.ametikoht,koosseis.ametikoht)= 
ametikoh.AmetiKoht
 LEFT JOIN isik ON iandmed.isik=isik.isik
WHERE true
ORDER BY 1,2


Revelant pars of table structures are:

CREATE TABLE iandmed
(
 reanr integer NOT NULL DEFAULT nextval('iandmed_reanr_seq'::regclass),
 isik character(10) NOT NULL,
 miskuup date,
 plopp date,
 summavrt numeric(12,2),
 kuluobj character(10),
 ametikoht numeric(7),
 CONSTRAINT iandmed_pkey PRIMARY KEY (reanr)
) ;


CREATE TABLE koosseis
(
 id numeric(7) NOT NULL,
 ametikoht numeric(7) NOT NULL,
 objekt1 character(10) NOT NULL,
 kogus numeric(4) NOT NULL DEFAULT 0,
 algus date,
 lopp date,
 CONSTRAINT koosseis_pkey PRIMARY KEY (id)
); 


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend