I need to convert a value $1,500,35 (American standard) to R$1.500,35 (Brazilian standard), but the language used in the bank is American and the functions I know only convert the value to the bank's current language. How can I do this?
Query example I'm using:
select (invoice_data->'valor_cobrado')::money as "Valor acumulado" from tb_invoice where invoice_number = 4653801
Query result: $ 14,825.00
I need to return this: $ 14,825.00
If you intend to do the direct conversion to the database, in select, you can use the command to_char(number, format) .
In format, using 0 means that house is required. Using 9 means that it is optional.
The character L indicates that a language should be used, G a group (',' in English, '.' in pt), and D indicates the decimal place. , ',' in pt-br)
Following this documentation , we see in the footnotes at the bottom of the page that some changes need to be made of locale no pg:
set lc_numeric to 'pt_BR';
set lc_monetary to 'pt_BR';
ALTER SESSION SET NLS_TERRITORY="BRAZIL";
To change the PgSQL encoding, I recommend reading this doc .
Here are some examples:
SELECT to_char(213456.789,'L000G000G000D99');
SELECT to_char(213456.789,'L999G999G990D99');
SELECT to_char(0.789,'L999G999G990D99');
SELECT to_char(.7,'L999G999G990D99');
Well. beautiful is not! But I could do the following:
SELECT REPLACE(REPLACE(REPLACE(REPLACE('$14,825.00'::text,'$','R$ '),',','|'),'.',','),'|','.')
In your example I think it should look like this:
select REPLACE(REPLACE(REPLACE(REPLACE(((invoice_data->'valor_cobrado')::money)::text,'$','R$ '),',','|'),'.',','),'|','.') as "Valor acumulado" from tb_invoice where invoice_number = 4653801
Understanding what is being done, in this alternative you would convert the money to text field, and hence make the character changes.
The ideal would be to change the locale of your postgres database to pt-br, so problems like this and / or the date format for the Brazil default would be solved.
I hope I have helped. Always seek Knowledge. :)