How to format number in PostgreSQL?

by tressie.damore , in category: PHP Databases , 2 years ago

How to format number in PostgreSQL?

Facebook Twitter LinkedIn Telegram Whatsapp

2 answers

Member

by lily , 2 years ago

@tressie.damore 

In PostgreSQL, you can use the to_char function to format a numeric value as a string. For example:

1
SELECT to_char(12345.678, '9999.99') as formatted_number;


This will return the string '12345.68'. The format string '9999.99' specifies that the number should be formatted with at least 4 digits before the decimal point and 2 digits after the decimal point.


You can use various formatting options to control the appearance of the formatted number. For example:

  • FM: suppresses leading and trailing zeros
  • $: adds a currency symbol
  • 9: a digit placeholder
  • 0: a zero placeholder
  • .: the decimal point
  • ,: the thousand separator


Here are some examples of how you can use these formatting options:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT to_char(12345.678, 'FM$9,999.99') as formatted_number;
-- Returns '$12,345.68'

SELECT to_char(12345.678, '$9,999.00') as formatted_number;
-- Returns '$12,345.68'

SELECT to_char(12345.678, '$9,999.00') as formatted_number;
-- Returns '$12,345.68'

SELECT to_char(12345.678, '$9,999.00') as formatted_number;
-- Returns '$12,345.68'

SELECT to_char(12345.678, '$9,999.90') as formatted_number;
-- Returns '$12,345.68'

SELECT to_char(12345.678, '$9,999') as formatted_number;
-- Returns '$12,346'

SELECT to_char(12345.678, 'FM$9,999') as formatted_number;
-- Returns '$12,346'

SELECT to_char(12345.678, 'FM$9,999.90') as formatted_number;
-- Returns '$12,345.68'


I hope this helps! Let me know if you have any questions.

by mallory_cormier , a year ago

@tressie.damore 

In PostgreSQL, you can format numbers using the to_char() function. This function accepts two arguments: the number to be formatted and a format string.


Here is an example of how to format a number in PostgreSQL:

1
SELECT to_char(1234.5678, '999,999.99');


This will output the number as "1,234.57".


In the format string, the "9" represents a digit and the "," represents a group separator. The "." is used to separate the integer and fractional parts of the number.


You can also include other characters in the format string, such as a currency symbol:

1
SELECT to_char(1234.5678, '€999,999.99');


This will output the number as "€1,234.57".


You can customize the format string to suit your specific formatting needs. The PostgreSQL documentation provides a complete list of format elements that you can use: https://www.postgresql.org/docs/current/functions-formatting.html