Re: How to create index on only some of the rows

On 2011-02-07, A B <gentosaker(at)gmail(dot)com> wrote:
> Hello.
>
> How do you create an index for only some of the rows in a table? I
> read in the docs:
>
> "The expression used in the WHERE clause can refer only to columns of
> the underlying table, but it can use all columns, not just the ones
> being indexed. Presently, subqueries and aggregate expressions are
> also forbidden in WHERE. The same restrictions apply to index fields
> that are expressions. "
>
> So until this changes, can you just add a boolean field to tell if the
> column should be used in the index, and then run "create index ....
> where use_in_index = true" or are there other (better?) ways of doing
> this?

the manual section quoted above is not exactly correct,
you are also allowed constants, immutable functions and operators.

create index foo_bigbar on foo(bar) where quux > 8.6;
create index foo_bar_nofred on foo(bar) where fred is null;

etc...

--
⚂⚃ 100% natural