Thanks for the reply - after a bit more poking around it seems that:
t1.col IS NOT DISTINCT FROM t2.col
should work - although I guess this means an upgrade from 8.1 to 8.3
Cheers,
Ian
On Wed, Apr 2, 2008 at 6:23 PM, Martijn van Oosterhout <
kleptog@svana.org> wrote:
On Wed, Apr 02, 2008 at 05:49:37PM +0100, Ian Sillitoe 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
You can't, NULL is not a value like other values.
> 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?
in the very specific case of comparing with a constant. The easiest is
to think of NULL as meaning 'unknown'. Clearly you can't copare that
usefully with anything.
Perhaps you can use a marker like -1 to acheive the effect you want?
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFH88EBIB7bNG8LQkwRAh7CAJ9ffmMnyE/OeJrTepSaOURb2WSRhACeMYql
tnrzLDVLyFfHhDqKiY02QOM=
=dhZf
-----END PGP SIGNATURE-----
