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 <[EMAIL PROTECTED]> 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? > > 'transform_null_equals' won't help you at all here since it only help > 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 <[EMAIL PROTECTED]> 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----- > >
