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

postgresql text indexing locale varchar
Matt Huggins picture Matt Huggins · Nov 3, 2009 · Viewed 22k times · Source

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

Vinko Vrsalovic picture Vinko Vrsalovic · Nov 3, 2009

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.

Related questions

Difference between text and varchar (character varying)

What's the difference between the text data type and the character varying (varchar) data types? According to the documentation If character varying is used without length specifier, the type accepts strings of any size. The latter is a PostgreSQL extension. …

Read More
Finding similar strings with PostgreSQL quickly

I need to create a ranking of similar strings in a table. I have the following table create table names ( name character varying(255) ); Currently, I'm using pg_trgm module which offers the similarity function, but I have an efficiency problem. …

Read More
Postgres: Convert varchar to text

I screwed up and created a column as a varchar(255) where that is no longer sufficient. I've read that varchar has no performance benefits over text on Postgres, and so would like to convert the varchar to a text column …

Read More