From: | "Brent Wood" <b(dot)wood(at)niwa(dot)co(dot)nz> |
---|---|
To: | <ian(dot)sillitoe(at)googlemail(dot)com> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: (FAQ?) JOIN condition - 'WHERE NULL = NULL' |
Date: | 2008-04-02 18:43:05 |
Message-ID: | 47F48A8A0200007B000120A8@gwia1.ham.niwa.co.nz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>>> "Ian Sillitoe" <ian(dot)sillitoe(at)googlemail(dot)com> 03/04/08 5:49 AM >>>
I'm trying to JOIN two tables (well a table and a resultset from a PL/pgsql
function) where a joining column can be NULL
In a join, no value can be ascribed to a null field, so the equivalence fails. You can do tests like IS NULL, which strictly speaking is test for meeting a condition (that of not having any value), not a test for equivalence. As (simplistically) the condition NULL does equal the condition NULL, (NULL = NULL) is true.
The simplest approach is perhaps to have a value which does not occur naturally (like -1), as a substitute for nulls in the relevant columns. I believe this can be achieved via a view in your case, (pun intended :-), but which may be less efficient if performance is an issue:
create view depth_v as
select d.id,
d.name,
case when (d.depth1 is null) then -1 else d.depth1 end as depth1,
case when (d.depth2 is null) then -1 else d.depth2 end as depth2,
case when (d.depth3 is null) then -1 else d.depth3 end as depth3,
case when (d.depth4 is null) then -1 else d.depth4 end as depth4,
case when (d.depth5 is null) then -1 else d.depth5 end as depth5
from depth_table d;
You could then join against this view instead of your underlying table, eg:
select c.* from get_cathcode('1.10.8') c JOIN depth_v t USING(depth1, depth2, depth3, depth4);
The view will not have any NULL values in the depth fields, so the join should work.
see: http://www.postgresql.org/docs/8.2/static/functions-conditional.html
(Incidentally, if you are storing bathymetry or CTD data, I'd be interested in seeing your db structures, as I may be doing some work in that area soon :-)
HTH,
Brent Wood
From | Date | Subject | |
---|---|---|---|
Next Message | Ian Sillitoe | 2008-04-02 18:43:27 | Re: (FAQ?) JOIN condition - 'WHERE NULL = NULL' |
Previous Message | Morris Goldstein | 2008-04-02 18:42:08 | Re: Can Postgres 8.x start if some disks containing tablespaces are not mounted? |