FULL JOIN is only supported with merge-joinable join conditions - Mailing list pgsql-general

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)
);