Re: Create an index with a sort condition

On Mar 29, 2006, at 2:57 PM, Simon Riggs wrote:

> On Wed, 2006-03-29 at 03:14 -0800, sylsau wrote:
>
>> I use PostgreSQL 8.1 and I would like create and index on a table's
>> field with a sort condition on this field.
>> For example, I have a table named books and I want create an index on
>> the fields title and id_book with an order by id_book descendant.
>>
>> I know how to create the index without this condition :
>>
>> create index book_index on books(id_book, title);
>>
>
> The index is already sorted and can be scanned forwards or backwards.

I believe he's talking about something like

CREATE INDEX books__id_title ON books(id_book, title DESC);

which of course we don't support. But you can define a custom set of
operators that work backwards and use those to define the index, and
then use them in the ORDER BY.

BTW, is there a TODO for this? Second request for it I've seen in a
week...
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461