| From: | Bertrand Petit <pgsql-sql(at)phoe(dot)frmug(dot)org> | 
|---|---|
| To: | PostgreSQL-sql <pgsql-sql(at)postgresql(dot)org> | 
| Subject: | Equality operators on NULL values | 
| Date: | 2003-08-24 01:28:15 | 
| Message-ID: | 20030824032815.A32448@memo.frmug.org | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-sql | 
	(in)Equality operators evaluates to NULL when any of their
arguments are NULL as summarized bellow.
	NULL=NULL ==> NULL
	1=NULL    ==> NULL
	1=1       ==> t
	1=2       ==> f
I have a case where I need to delete rows from table A for records
that are designated in table B. One of the joined colum can have NULL
values. Therefore I need an operator behaving as follows:
	NULL=NULL ==> t
	1=NULL    ==> NULL (or anything else as long as it is not t)
	1=1       ==> t
	1=2       ==> f
I replaced the use of the = operator with this expression:
nullif(tabA.col_with_nulls, tabB.col_with_nulls) IS NULL
It works correctly but the intent is not that clear.
	Is there a stock replacement operator that would behave like
the second truth table?
	Regards,
	Bertrand.
-- 
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Peter Eisentraut | 2003-08-24 08:43:57 | Re: Equality operators on NULL values | 
| Previous Message | Jeff Eckermann | 2003-08-23 16:55:44 | Re: Delete denied? |