0
I need to convert a value $1,500,35 (american standard), for 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 current language of the bank. How can I do that?
Example of query 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: R $ 14.825,00
1
If you intend to do the conversion directly in the database, in select, you can use the command to_char(number, format).
In the format, use 0 means that box is mandatory. Use 9 means it is optional.
The character L indicates that a language should be used, G a group (',' in English, '.' in en) and D indicates the decimal place ('. ' in English, ',' in English)
Following that documentation, we see in the footnotes at the bottom of the page that some locale changes need to be made in 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 of this doc.
Following are examples:
SELECT to_char(213456.789,'L000G000G000D99');
SELECT to_char(213456.789,'L999G999G990D99');
SELECT to_char(0.789,'L999G999G990D99');
SELECT to_char(.7,'L999G999G990D99');
When Testing the above examples: ERROR: Multiple decimal points ******** Error *********** ERROR: Multiple decimal points SQL state: 42601
I updated the answer! I dug a little deeper into the subject, and I borrowed a typo (I had swapped the’D' with the 'G' in the format).
It worked perfectly as expected, had already read this documentation but had not been as clear as its explanation, thank you very much.
Where do I put the mentioned settings? I don’t understand where this locale is
0
Well. It’s not pretty! But I could do this:
SELECT REPLACE(REPLACE(REPLACE(REPLACE('$14,825.00'::text,'$','R$ '),',','|'),'.',','),'|','.')
In your example I believe you should stay 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 field to text, and then make the character changes.
The ideal would be to change the locale of your bank postgres, for en, in this way problems such as this and/or the date format for the Brazil standard would be solved.
I hope I helped. Always Seek the Knowledge. :)
Browser other questions tagged postgresql
You are not signed in. Login or sign up in order to post.
It may be just because it’s not clear, but it doesn’t seem to make sense. Show better what you want.
– Maniero
American standard, and database:
000,000,000.00. in the language, follows the same pattern, will only change in the display, according to what you choose... you should at least tell what language you are using and show the code you want to implement– Rovann Linhalis
particularly, I use Numeric, not money... I leave the formatting to the application side
– Rovann Linhalis