Glyn Astill <glynastill@yahoo.co.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!