Cheers,
Ian
On Wed, Apr 2, 2008 at 5:49 PM, Ian Sillitoe <ian.sillitoe@xxxxxxxxxxxxxx> wrote:
This is probably a stupid question that has a very quick answer, however it would be great if someone could put me out of my misery...
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
-- for a given id, return a bunch of columns that I can use for joins
psql> select * from get_colnames_for_id('1.10.8');
depth1 | depth2 | depth3 | depth4 | depth5 |
--------+--------+--------+--------+--------+
1 | 10 | 8 | | |
(1 row)
--
psql> select * from table where depth1 = 1 AND depth2 = 10 AND depth3 = 8 AND depth4 IS NULL;
id | depth1 | depth2 | depth3 | depth4 | depth5 | name |
---+--------+--------+--------+--------+--------+----------------------+
1 | 1 | 10 | 8 | | | name for node 1.10.8 |
(1 row)
-- I (wrongly) expected the following to be equivalent
psql> select c.* from get_cathcode('1.10.8') c JOIN table t USING(depth1, depth2, depth3, depth4);
id | depth1 | depth2 | depth3 | depth4 | depth5 | name |
---+--------+--------+--------+--------+--------+-----------+
(0 rows)
-- Whereas the following works...
psql> select * from get_colnames_for_id('1.10.8.10');
depth1 | depth2 | depth3 | depth4 | depth5 |
--------+--------+--------+--------+--------+
1 | 10 | 8 | 10 | |
(1 row)
psql> select c.* from get_cathcode('1.10.8.10') c JOIN table t USING(depth1, depth2, depth3, depth4);
id | depth1 | depth2 | depth3 | depth4 | depth5 | name |
---+--------+--------+--------+--------+--------+-------------------------+
2 | 1 | 10 | 8 | 10 | | name for node 1.10.8.10 |
(0 rows)
So, I'm currently assuming from all this that joining on t1.col = t2.col doesn't make any sense when t1.col and t2.col are both NULL - since:
psql> SELECT (NULL = NULL) IS TRUE;
?column?
----------
f
(1 row)
psql> SELECT (NULL IS NULL) IS TRUE;
?column?
----------
t
(1 row)
Unless I've missed something, the docs on http://www.postgresql.org/docs/8.1/static/functions-comparison.html seem to suggest that the concept is an example of bad programming and the workaround (of switching on the 'transform_null_equals' config) is a hack. Is this all true or did my logic just get screwed up at some point? Unless I've just missed something obvious, it seems useful to be able to join two tables based on a condition where they share a NULL column - is there another way of doing this?
Many thanks,
Ian
