On Wed, Nov 09, 2005 at 12:37:25PM +0200, Andrus wrote: > I'm using Postgres 8.1 in Windows. Database encoding is UTF-8 > lc_ctype is Estonian_Estonia.1257. > lc_collate is Estonian currently. However I can set lc_collate to C if this > solves this issue. > > Doc says that > " to allow PostgreSQL to use indexes with LIKE clauses under a non-C locale, > several custom operator classes exist" > > I don't understand "non-C locale". Does this mean lc_collate or also some > other lc_ setting ? lc == locale. There are several different locale settings but collation affects ordering. And Estonian is not C (obviously). > I need to optimize queries with variable number of characters in beginning > like > > SELECT ... WHERE substring( col1 for 1 ) = 'f' > SELECT ... WHERE substring( col1 for 2 ) = 'fo' > SELECT ... WHERE substring( col1 for 3 ) = 'foo' > etc If you use queries like: SELECT ... WHERE col1 LIKE 'fo%' it can use an index declared like: CREATE INDEX myindex on mytable(col1 text_pattern_ops); > In my current dbms, Microsoft Visual FoxPro I have a single index > > CREATE INDEX i1 ON mytable(col1) > > I can use queries: > > WHERE col1 BETWEEN 'f' and 'f'+CHR(255) > WHERE col1 BETWEEN 'fo' and 'fo'+CHR(255) > WHERE col1 BETWEEN 'foo' and 'foo'+CHR(255) Well, you could do that in postgresql too, you just need to use the SQL standard concatination operator. WHERE col1 BETWEEN 'f' and 'f' || chr(255); > How to get same functionality in Postgres ? > Does there exist unicode special character which is greater than all other > chars ? Umm, I don't think so. Order is defined by the locale, not the character set. My guess is that text_pattern_ops is the way to go. Have a nice day, -- Martijn van Oosterhout <kleptog@xxxxxxxxx> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment:
pgpQNVN00sNC4.pgp
Description: PGP signature
