Re: Index not used with IS NULL

What is the problem with indexing nulls?
I had the similar problem some time ago, and created a custom set of
operators as a work around (that do the same thing as <=> for numbers,
but treat null as infinity and '=' returns true if both operand are
null, and false if only one is)...
It seems to work fine.
The only problem is, that it is kinda cumbersome to create custom
opclasses in postgres, and also, that I don't want to create the same
wrappers for all possible types (int2,int4,int8,float etc)...

It would be a lot nicer if the default operators could handle that...
Why can it not be done?

Thanks!

Dima

Tom Lane wrote:
> Nick Wellnhofer <wellnhofer(at)aevum(dot)de> writes:
>
>>If I have a query like
>>SELECT * FROM table WHERE key IS NULL
>>and an index on column "key", a sequential scan is used.
>
>
> IS NULL is not an indexable operator.
>
> I suggest reconsidering your data representation, as this is unlikely to
> change soon...
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org