Ask Question Sign In Sign Up

How to format number in PostgreSQL?

Member

by tressie.damore , in category: PHP Databases , 9 months ago

How to format number in PostgreSQL?

Facebook Twitter LinkedIn Telegram Whatsapp

1 answer

Member

by lily , 3 months 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.

Related Threads:

How to add 0 in front of number in PostgreSQL?
How to format a date in PHP?
How to get month in PostgreSQL?
How to concatenate strings in PostgreSQL?
How to drop trigger in PostgreSQL?
How to login as superuser in PostgreSQL?