I get it. Thanks, Tom. In case anybody else needs this functionality, let me offer a family of functions that do comparisons that treat NULL as a real value (rather than as "unknown"). For example: CREATE OR REPLACE FUNCTION eqnull(varchar, varchar) RETURNS boolean AS $BODY$ /* Return True if both args have the same non-NULL values or both args are NULL; otherwise False. */ DECLARE v1 ALIAS FOR $1; v2 ALIAS FOR $2; BEGIN -- NULL = NULL IF v1 IS NULL AND v2 IS NULL THEN RETURN True; -- NULL != Any non-NULL value ELSIF v1 IS NULL AND v2 IS NOT NULL THEN RETURN False; -- Any non-NULL value != NULL ELSIF v1 IS NOT NULL AND v2 IS NULL THEN RETURN False; -- Non-NULL value = non-NULL value ELSIF v1 = v2 THEN RETURN True; -- Non-NULL value != non-NULL value ELSE RETURN False; END IF; END; $BODY$ LANGUAGE plpgsql VOLATILE ; You need a separate function for each data type you want to compare; the only difference among these functions is their argument types. I've implemented variants for VARCHAR, NUMERIC, TIMESTAMP, and BOOLEAN. The reason I need this is that I'm writing functions to test my database programming (triggers, rules, etc), and these tests sometimes need to treat Null as just another value in doing test comparisons. ~ Ken > -----Original Message----- > From: Tom Lane [mailto:tgl@xxxxxxxxxxxxx] > Sent: Saturday, June 05, 2010 9:41 AM > To: Ken Winter > Cc: 'PostgreSQL pg-general List' > Subject: Re: Null comparisons and the transform_null_equals run- > time parameter > > "Ken Winter" <ken@xxxxxxxxxxx> writes: > > When the run-time parameter transform_null_equals is on, shouldn't two > > variables with NULL values evaluate as equal? > > No. That setting does not change the runtime behavior of comparison. > The only thing it does is change the literal syntax "something = NULL" > to "something IS NULL". > > regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
