Need efficient way to do comparison with NULL as an option

Поиск
Список
Период
Сортировка
От D. Dante Lorenso
Тема Need efficient way to do comparison with NULL as an option
Дата
Msg-id 477F0262.3000906@lorenso.com
обсуждение исходный текст
Ответы Re: Need efficient way to do comparison with NULL as an option  ("D. Dante Lorenso" <dante@lorenso.com>)
Re: Need efficient way to do comparison with NULL as an option  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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


В списке pgsql-general по дате отправления:

Предыдущее
От: "Joshua D. Drake"
Дата:
Сообщение: Re: large table vacuum issues
Следующее
От: Greg Smith
Дата:
Сообщение: Re: Performance problem. Could it be related to 8.3-beta4?