The locale settings influence the following SQL features:
-
Sort order in queries using
ORDER BY
or the standard comparison operators on textual data -
Pattern matching operators (
LIKE
,SIMILAR TO
, and POSIX-style regular expressions); locales affect both case insensitive matching and the classification of characters by character-class regular expressions -
The ability to use indexes with
LIKE
clauses
The drawback of using locales other than C
or POSIX
in PostgreSQL is its performance impact. It slows character handling and prevents ordinary indexes from being used by LIKE
. For this reason use locales only if you actually need them.
As a workaround to allow PostgreSQL to use indexes with LIKE
clauses under a non-C locale, several custom operator classes exist. These allow the creation of an index that performs a strict character-by-character comparison, ignoring locale comparison rules. Refer to Section 11.10 for more information. Another approach is to create indexes using the C
collation, as discussed in Section 23.2.