Need efficient way to do comparison with NULL as an option

From: "D(dot) Dante Lorenso" <dante(at)lorenso(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Need efficient way to do comparison with NULL as an option
Date: 2008-01-05 04:06:58
Message-ID: 477F0262.3000906@lorenso.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

All,

I'm looking for an operator that will compare NULL with NULL and
evaluate as TRUE.

I have a BIGINT column which might contain NULL values. I want to pass
a value to compare with that column in my WHERE clause. If the value
I'm comparing is 0, I want it to match the NULL values. Here is a
sample query that I currently use:

SELECT *
FROM mytable
WHERE (col IS NULL AND NULLIF(?, 0) IS NULL) OR col = ?;

The '?' placeholders used in the query will receive the same value which
might be any one of the following: NULL, 0, 1, 2, 3, etc.

What I'd really like is an operator that will compare NULL with NULL and
evaluate as TRUE. Does that exist?

I tried solving this myself, so I have a stored proc like this:

--------------------
CREATE OR REPLACE FUNCTION "public"."is_equal_bigint" (in_val1 bigint,
in_val2 bigint) RETURNS boolean AS
$body$
BEGIN
-- both values are null
IF in_val1 IS NULL AND in_val2 IS NULL THEN
RETURN TRUE;
END IF;

-- values are the same
IF in_val1 = in_val2 THEN
RETURN TRUE;
END IF;

-- values are different
RETURN FALSE;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;
--------------------

I can use this function as follows:

SELECT *
FROM mytable
WHERE is_equal_bigint(col, NULLIF(?, 0)) IS TRUE;

But I worry that this will not allow my query to use any indexes on
'col' and will make all my queries slower.

Any recomendations for making my first query above more optimized and
still efficient? Does the operator I'm looking for exist?

-- Dante

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Greg Smith 2008-01-05 04:09:31 Re: Performance problem. Could it be related to 8.3-beta4?
Previous Message Joshua D. Drake 2008-01-05 03:21:09 Re: large table vacuum issues