How do I know if my PostgreSQL server is using the "C" locale?

Question

I'm trying to optimize my PostgreSQL 8.3 DB tables to the best of my ability, and I'm unsure if I need to use varchar_pattern_ops for certain columns where I'm performing a LIKE against the first N characters of a string. According to this documentation, the use of xxx_pattern_ops is only necessary "...when the server does not use the standard 'C' locale".

Can someone explain what this means? How do I check what locale my database is using?

Answer 1

Currently some locale [docs] support can only be set at initdb time, but I think the one relevant to _pattern_ops can be modified via SET at runtime, LC_COLLATE. To see the set values you can use the SHOW command.

For example:

SHOW LC_COLLATE 

_pattern_ops indexes are useful in columns that use pattern matching constructs, like LIKE or regexps. You still have to make a regular index (without _pattern_ops) to do equality search on an index. So you have to take all this into consideration to see if you need such indexes on your tables.

About what locale is, it's a set of rules about character ordering, formatting and similar things that vary from language/country to another language/country. For instance, the locale fr_CA (French in Canada) might have some different sorting rules (or way of displaying numbers and so on) than en_CA (English in Canada.). The standard "C" locale is the POSIX standards-compliant default locale. Only strict ASCII characters are valid, and the rules of ordering and formatting are mostly those of en_US (US English)

In computing, locale is a set of parameters that defines the user's language, country and any special variant preferences that the user wants to see in their user interface. Usually a locale identifier consists of at least a language identifier and a region identifier.

Answer 2

psql -l

according to handbook

example output:

                               List of databases     Name     | Owner  | Encoding |   Collate   |    Ctype    | Access privileges -------------+--------+----------+-------------+-------------+-------------------  packrd      | packrd | UTF8     | en_US.UTF-8 | en_US.UTF-8 |  postgres    | packrd | UTF8     | en_US.UTF-8 | en_US.UTF-8 |  template0   | packrd | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/packrd        +              |        |          |             |             | packrd=CTc/packrd  template1   | packrd | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/packrd        +              |        |          |             |             | packrd=CTc/packrd (5 rows)