CODEWITHSUNDEEP
postgresqlcollate
cmgpimenta
cmgpimenta

Reputation: 21

Problems with COLLATE in PostgreSQL 12

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.

Upvotes: 2

Views: 1911

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246043

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');

Upvotes: 1

Related Questions