Re: Index order

Glyn Astill <glynastill(at)yahoo(dot)co(dot)uk> writes:

> I'd expect the rows starting with the caret to appear either at the start or
> end of, rather than in the middle, it appears as if the index ignores them.
>
> Database locale is Latin1

Latin1 isn't a locale, it's a character set (and an encoding).

Your locale is probably something like en_US which generally sorts as you show
above. It sounds like you're expecting the C locale sorting which is the ascii
order.

You can either re-initdb your database with locale set to C, or on recent
versions of Postgres you can use "ORDER BY artist USING ~>~" which
specifically sorts based on the binary ascii order.

If you want the query to use an index you would have to built it with
something like:

create index idx on foo (artist text_pattern_ops);

Note that this ordering won't do anything sane with any accented characters or
in latin1 since they'll be sorted by their binary value which comes after all
the unaccented characters.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!