FULL JOIN is only supported with merge-joinable join conditions
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)
);
"Andrus" <kobruleht2@hot.ee> writes:
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
SELECT
...
FROM iandmed
FULL JOIN koosseis ON iandmed.ametikoht=koosseis.ametikoht
AND iandmed.Kuluobj= koosseis.objekt1
AND iandmed.AmetiKoht is not null
Uh, can't you just drop the "iandmed.AmetiKoht is not null" condition?
It seems redundant considering that "iandmed.ametikoht=koosseis.ametikoht"
isn't going to succeed when ametikoht is null.
In the long run we should teach hash join to support full-join behavior,
which would allow cases like this one to work; but it seems not very
high priority, since I've yet to see a real-world case where a
non-merge-joinable full-join condition was really needed. (FULL JOIN
being inherently symmetric, the join condition should usually be
symmetric as well...)
regards, tom lane
"Andrus" <kobruleht2@hot.ee> writes:
I've yet to see a real-world case where a
non-merge-joinable full-join condition was really needed.
I need to eliminate rows containing null value in left side table in full
join.
create table iandmed ( ametikoht integer );
insert into iandmed values(1);
insert into iandmed values(null);
create table koosseis (ametikoht integer );
insert into koosseis values(2);
SELECT *
FROM iandmed
FULL JOIN koosseis ON iandmed.ametikoht=koosseis.ametikoht
AND iandmed.ametikoht IS NOT NULL
Required result:
1 null
null 2
Well, if we did support that query as written, it would not produce the
result you want. With or without the IS NOT NULL part, the
null-containing row of iandmed will fail to join to every row of
koosseis, and will therefore produce a single output row with nulls for
the koosseis field(s). If you get a different result in some other
database, it's broken (nonstandard handling of NULL comparison maybe?).
I think the way to get the result you want is to suppress the
null-containing rows before they get to the FULL JOIN, like so:
regression=# SELECT *
FROM (SELECT * FROM iandmed WHERE ametikoht IS NOT NULL) AS iandmed
FULL JOIN koosseis ON iandmed.ametikoht=koosseis.ametikoht;
ametikoht | ametikoht
-----------+-----------
1 |
| 2
(2 rows)
regards, tom lane
Import Notes
Reply to msg id not found: 003001c79b23$938954c0$4906eb50@acer
I think the way to get the result you want is to suppress the
null-containing rows before they get to the FULL JOIN, like so:regression=# SELECT *
FROM (SELECT * FROM iandmed WHERE ametikoht IS NOT NULL) AS iandmed
FULL JOIN koosseis ON iandmed.ametikoht=koosseis.ametikoht;
Thank you.
In my case koosseis.ametikoht column does not contain null values.
Si I fixed this in WHERE clause
WHERE (iandmed.ametikoht is not null or koosseis.ametikoht is not null)
I hope this produces same result in my case.
Andrus.