pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Locale and pattern matching 2+ messages / 2 participants [nested] [flat]
* Locale and pattern matching @ 2005-12-08 01:37 Hélder M. Vieira <hmv@mail.telepac.pt> 0 siblings, 1 reply; 2+ messages in thread From: Hélder M. Vieira @ 2005-12-08 01:37 UTC (permalink / raw) To: pgsql-sql A question about encoding, some days ago, led me to concentrate my attention on the subject and perform a few tests on two different 8.1 installations, configured as follows: Machine 1 - pt_PT(Portuguese_Portugal.28591)/LATIN1 Machine 2 - pt_PT(Portuguese_Portugal.28605)/LATIN9 In both machines, case insensitive pattern matching designed to match the letter 'A' will indeed match 'A' and 'a' but not the accented forms. Generalizing, an accented or unaccented vowel in the pattern will only match that vowel in upper or lowercase forms if the accent is the same as specified in the pattern. For instance, if the pattern specifies an uppercase 'A' with an accute accent, then it will match a lowercase 'A' with an accute accent, but not an upper or lower case 'A' with a tilde. This behaviour seems inconsistent with that of the ORDER BY clause, which considers all forms of a vowel as equal (uppercase/lowercase,accented/not accented). Shouldn't case insensitive pattern matching follow the same collation weights that ORDER BY uses ? Can someone please elaborate on this subject ? Another question, for european users: Until now, I've been selecting LATIN1 encoding, but after a few tests, I came to think that LATIN9 is a better option (the euro sign...). For those who regularly use LATIN9, what is your opinion ? Is it indeed a better option ? Thank you. Hélder M. Vieira LATIN CAPITAL LETTER A WITH GRAVE ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Locale and pattern matching @ 2005-12-08 08:21 Peter Eisentraut <peter_e@gmx.net> parent: Hélder M. Vieira <hmv@mail.telepac.pt> 0 siblings, 0 replies; 2+ messages in thread From: Peter Eisentraut @ 2005-12-08 08:21 UTC (permalink / raw) To: pgsql-sql; +Cc: Hélder M. Vieira <hmv@mail.telepac.pt> Hélder M. Vieira wrote: > For instance, if the pattern specifies an uppercase 'A' with an > accute accent, then it will match a lowercase 'A' with an accute > accent, but not an upper or lower case 'A' with a tilde. > This behaviour seems inconsistent with that of the ORDER BY clause, > which considers all forms of a vowel as equal This is not exactly true. Sorting just goes through the strings in multiple passes, looking at the "general" letter first, then at the case, then at the accents, until it finds a difference. > Shouldn't case insensitive pattern matching follow the same collation > weights that ORDER BY uses ? Even if it did, it would not help you, as explained above. Moreover, the collation rules of some locales are sufficiently weird that a consistent behavior between sorting and pattern matching is impossible to achieve. > Until now, I've been selecting LATIN1 encoding, but after a few > tests, I came to think that LATIN9 is a better option (the euro > sign...). For those who regularly use LATIN9, what is your opinion ? > Is it indeed a better option ? Yes. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-12-08 08:21 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2005-12-08 01:37 Locale and pattern matching Hélder M. Vieira <hmv@mail.telepac.pt> 2005-12-08 08:21 ` Peter Eisentraut <peter_e@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox