simon godden wrote:
If the index isn't used, then we have problem #3. I think this is what
you are actually seeing. Your locale is something other than "C" and PG
doesn't know how to use like with indexes. Read up on operator classes
or change your locale.
http://www.postgresql.org/docs/8.1/static/indexes-opclass.html
Aha - that sounds like it - this is the output from locale
LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
..
I guess it cannot determine the collating sequence?
It can, but isn't sure that it can rely on LIKE 'A%' being the same as
>= 'A' and < 'B' (not always true). Re-creating the index with the
right opclass will tell it this is the case.
I'm not too familiar with unix locale issues - does this output match
your problem description?
OK - quick intro to locales. Create a file /tmp/sortthis containing the
following:
---begin file---
BBB
CCC
AAA
A CAT
A DOG
ACAT
---end file---
Now run "sort /tmp/sortthis". You'll probably see spaces get ignored.
Now run "LANG=C sort /tmp/sortthis". You'll probably see a traditional
ASCII ("C") sort. If not try LC_COLLATE rather than LANG.
Can you explain how to change my locale to 'C'? (I'm quite happy for
you to tell me to RTFM, as I know this is not a linux user mailing
list :)
You'll want to dump your databases and re-run initdb with a locale of
"C" (or no locale). See:
http://www.postgresql.org/docs/8.1/static/app-initdb.html
That will mean all sorting will be on ASCII value. The problem is that
the database picks up the operating-system's default locale when you
install it from package. Not always what you want, but then until you
understand the implications you can't really decide one way or the other.
HTH
--
Richard Huxton
Archonet Ltd
