What specifically do locale settings do, and is there a way for me to test the effects of different locales?

Question

I might need to change the locales settings of lc_messages, lc_monetary, lc_numeric, lc_time in my postgresql config. However, I need to know what specifically they affect before I do that. The postgresql documentation gives a list of what's affected, but it still leaves a lot of questions open. For example, it says that upper and lower functions are affected. Is that true for all the settings? Or only for some of them? It also says that to_char() is affected. How, excatly?

I want to know ahead of time if this change will require changing the app code, so is there a good resource to help me generate a list of features/scenarios to test?

score 5 · Accepted Answer · answered Apr 19 '25 at 13:34

What affects upper() and lower() is the LC_CTYPE setting. For example, if I create the following database on my Linux system (the collation names on other operating systems might be different):

CREATE DATABASE x
   TEMPLATE template0
   LOCALE_PROVIDER libc
   LC_COLLATE "C"
   LC_CTYPE "tr_TR.utf8";

then I get the following:

SELECT upper('izmir');
upper
═══════
 İZMİR

But I get IZMIR with different LC_CTYPE settings.

LC_CTYPE also affects how a character is classified. For example, with LC_CTYPE "C", Arabian numerals won't be classified as numerals:

SELECT '٢' ~ '[[:digit:]]';
?column? 
══════════
 f

LC_COLLATE determines how strings are sorted and compared. For example, look at the following comparison in the English and the Czech collation:

SELECT 'ch' COLLATE "en_US.utf8" < 'd',
       'ch' COLLATE "cs_CZ.utf8" < 'd';
?column? │ ?column? 
══════════╪══════════
 t        │ f

LC_MESSAGES determines the language used by server error messages and in the server log.

SET lc_messages = 'zh_CN.utf8';
SELECT 1 / 0;
错误:  除以零

LC_TIME determines how to_char() renders localized datetime format strings:

SET lc_time = 'tr_TR.utf8';
SELECT to_char(DATE '2010-04-01', 'TMDay, DD TMMon YYYY');
    to_char        

═══════════════════════
 Perşembe, 01 Nis 2010

LC_NUMERIC determines localized decimal and group separators in to_char() for numbers:

SET lc_numeric = 'de_AT.utf8';
SELECT to_char(3000.1415, '9G999D99999');
to_char

══════════════
  3.000,14150

LC_MONETARY determines the currency symbol used by to_char() and the data type money:

SET lc_monetary = 'en_GB.utf8';
SELECT to_char(12, 'L00');
to_char 
═════════
 £ 12
SELECT CAST (12 AS money);
money

════════
 £12.00

What specifically do locale settings do, and is there a way for me to test the effects of different locales?

1 Answers1