Problems with COLLATE in PostgreSQL 12

Question

My problem:

I work in Windows 10 and my computer is set-up to Portuguese (pt_BR);

I'm building a database in PostgreSQL where I need certain columns to remain in Portuguese, but others to be in en_US - namely, those storing numbers and currency. I need $ instead of R$ and 1,000.00 instead of 1.000,00.

I tried to create columns this way using the COLLATE statement, as:

CREATE TABLE crm.TESTE (
    prodserv_id varchar(30) NOT NULL,
    prodserv_name varchar(140) NULL,
    fk_prodservs_rep_acronym varchar(4) NULL,
    prodserv_price numeric null collate "en_US",
    CONSTRAINT pk_prodservs_prodserv_id PRIMARY KEY (prodserv_id)
);

But I get the error message:

SQL Error [42704]: ERROR: collation "en_US" for encoding "UTF8" does not exist

Database metadata shows Default Encoding: UTF8 and Collate Portuguese_Brazil.1252

It will be deployed at my ISP, which runs Linux.

Any suggestions would be greatly appreciated. Thanks in advance.

My first post here... why no paragraphs? – cmgpimenta Jan 09 '20 at 23:03 — cmgpimenta, Jan 09 '20 at 23:03
`collate` for a numeric column makes no sense at all – Jan 10 '20 at 07:59 — , Jan 10 '20 at 07:59

score 1 · Answer 1 · answered Jan 10 '20 at 07:46

A collation defines how strings are compared. It is not applicable to numerical data.

Moreover, PostgreSQL uses the operating system's collations, which causes problems when porting a database from Windows to other operating systems. The collation would be called English on Windows and en_US.utf8 on operating systems that use glibc.

To influence the formatting of numbers and currency symbols, set the lc_numeric and lc_monetary parameters appropriately (English on Windows, en_US elsewhere). Note that while lc_monetary affects the string representation of the money data type, these settings do not influence the string representation of numbers. You need to use to_char like this:

to_char(1000, '999G999G999D00 L');

Thank you very much! It seems the problem was that I used en_US instead of English. Thank you for your kind and prompt response. — cmgpimenta, Jan 13 '20 at 16:30

Problems with COLLATE in PostgreSQL 12

1 Answers1