Number format equivalent function in PostgreSQL

Question

Trying to format the number as per the given format and culture.

Given:

-4059587.225000, --Value
'#,##0.00;(#,##0.00)' --Format
'en-US' --Culture

Will have many patterns, the given one is for example.

Expected output: (4,059,587.23)

In SQL Server we have format() function, what's the equivalent in PostgreSQL?

My try:

select to_char( -4059587.225000, '#,##0.00;(#,##0.00)' );

Error:

multiple decimal points

Laurenz Albe · Accepted Answer · 2022-11-18T06:37:38.037

2

Use to_char:

SET lc_numeric = 'en_US';

SELECT translate(to_char(-4059587.225000, '9G999G999D99PRFM'), '<>', '()');

   translate    
════════════════
 (4,059,587.23)
(1 row)

The documentation describes the available formats.

edited Nov 18 '22 at 06:37

answered Nov 17 '22 at 13:29

Laurenz Albe

209,280
17
206
263

Can you please add link to find more patterns/format like `9G999G999D99PRFM`? – MAK Nov 18 '22 at 05:14
1

I have added links to the documentation. – Laurenz Albe Nov 18 '22 at 06:37

score 0 · Answer 2 · answered Nov 17 '22 at 13:29

0

How about:

select 
concat( to_char( -4059587.225000, '#,##0.00;' ), to_char( -4059587.225000, '(#,##0.00)' ) );

answered Nov 17 '22 at 13:29

Reto

1,305
1
18
32

Number format equivalent function in PostgreSQL

2 Answers2