John Sidney-Woollett wrote:
For what it's worth, we have a unicode 7.4.1 database which gives us the sorting and searching behaviour that we expect (with the exception of the upper and lower functions). We access the data via jdbc so we don't have to deal with encoding issues per se as the driver does any translation for us.
Currently we don't use any LIKE statements, but if we did, and wanted them optimized then we'd use the appropriate OP Class when defining the index. We also don't use any REGEX expressions. And we'll shortly be experimenting with tsearch2...
List of databases Name | Owner | Encoding ---------------+----------+---------- test | postgres | UNICODE
Setting the psql client encoding to Latin1 and inserting the following data...
# select * from johntest; id | value ----+------- 1 | test 2 | tést 3 | tèst 4 | taste 5 | TEST 6 | TÉST 7 | TÈST 8 | TASTE (8 rows)
and then extracting the data in sorted order works as we would expect
# select * from johntest order by value (no index on the value field) id | value ----+------- 8 | TASTE 5 | TEST 7 | TÈST 6 | TÉST 4 | taste 1 | test 3 | tèst 2 | tést (8 rows)
however, applying the UPPER function to the data does not work as expected, problem with ids 6,7,3,2 - should be ordered (3,7 or 7,3) , (6,2 or 2,6)
# select * from johntest order by upper(value); id | value ----+------- 4 | taste 8 | TASTE 1 | test 5 | TEST 7 | TÈST 6 | TÉST 3 | tèst 2 | tést (8 rows)
using a LIKE operation also works as expected (again no index on value field)
# select * from johntest where value like 't%'; id | value ----+------- 1 | test 2 | tést 3 | tèst 4 | taste (4 rows)
Like works, but it can't use an index, and so would have horibble performance vs. the situation where it CAN use an index. I believe this is how Postgres is working now.
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
