Re: Feature request: smarter use of conditional indexes

hi,

John Siracusa wrote, On 3/3/2004 20:56:

> Given an index like this:
>
>     CREATE UNIQUE INDEX i1 ON t1 (c1) WHERE c1 IS NOT NULL;
>
> and a query like this:
>
>     SELECT * FROM t1 WHERE c1 = 123;
>
> I'd like the planner to be smart enough to use an index scan using i1.  Yes,
> I can change the query to this:
>
>     SELECT * FROM t1 WHERE c1 = 123 AND c1 IS NOT NULL;
>
> In which case the index will be used, but I shouldn't have to.  More
> practically, since a lot of my SQL is auto-generated, it's difficult to make
> this query change just in the cases where I need it.  And I'm loathe to
> change every "column = value" pair in my auto-generated SQL into a double
> pair of "(column = value and column is not null)"  It's redundant and looks
> pretty silly, IMO.

how about: CREATE UNIQUE INDEX i1 ON t1 (c1);
WHERE c1 IS NOT NULL in this case what is the point of doing this?
You do not need this condition.

C.