Re: How to create index on only some of the rows - Mailing list pgsql-general

On 2011-02-07, A B <gentosaker@gmail.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