Glyn Astill <glynastill@xxxxxxxxxxx> 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!
