From: Bertrand Petit <pgsql-sql@phoe.frmug.org>
To: PostgreSQL-sql <pgsql-sql@postgresql.org>
Subject: Equality operators on NULL values
Date: Sun, 24 Aug 2003 03:28:15 +0200
Message-ID: <20030824032815.A32448@memo.frmug.org> (raw)
(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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pgsql-sql@phoe.frmug.org
Subject: Re: Equality operators on NULL values
In-Reply-To: <20030824032815.A32448@memo.frmug.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox