Thread: LIKE.....and Locale
PostgreSQL 8.0.3 Fedora Core4
I have moved a database from 7.4.5 to 8.0.3. My land setting when initdb'ing the 8.0.3 cluster was:
LANG=en_US.UTF-8
From recollection, under 7.4.5 my collate sequence etc in the config (although commented out) was 'C'.
The issue is under 7.4.5 the LIKE 'A%' used indexes to find data - under the new 8.0.3 database it doesn't. Two questions: 1) should LIKE 'A%' use an index under the en_US locale? 2) if not is the only way to correct this is to export the databases, re init with a different locale ('C'?) and then restore?
I have moved a database from 7.4.5 to 8.0.3. My land setting when initdb'ing the 8.0.3 cluster was:
LANG=en_US.UTF-8
From recollection, under 7.4.5 my collate sequence etc in the config (although commented out) was 'C'.
The issue is under 7.4.5 the LIKE 'A%' used indexes to find data - under the new 8.0.3 database it doesn't. Two questions: 1) should LIKE 'A%' use an index under the en_US locale? 2) if not is the only way to correct this is to export the databases, re init with a different locale ('C'?) and then restore?
| Regards, Steve Tucknott ReTSol Ltd DDI: 01903 828769 Mobile: 0773 671 5772 |
Steve Tucknott <steve@retsol.co.uk> writes: > The issue is under 7.4.5 the LIKE 'A%' used indexes to find data - under > the new 8.0.3 database it doesn't. Two questions: 1) should LIKE 'A%' > use an index under the en_US locale? No. See text_pattern_ops et al under http://www.postgresql.org/docs/8.0/static/indexes-opclass.html regards, tom lane