Re: Null comparisons and the transform_null_equals run-time parameter

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(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Saturday, June 05, 2010 9:41 AM
> To: Ken Winter
> Cc: 'PostgreSQL pg-general List'
> Subject: Re: [GENERAL] Null comparisons and the transform_null_equals run-
> time parameter
>
> "Ken Winter" <ken(at)sunward(dot)org> 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