PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to show comma separated numbers in Postgres

I have a table like this:

|     size      |
|---------------|
| 2880129015916 |
|          2560 |
|             0 |
|      91570752 |
|          8192 |
|      21037156 |

and I want to display the values as comma separated, for example, 2880129015916 will be shown as 2,880,129,015,916.

I already saw an elegant solution on SO, Comma separating numbers without advance knowledge of number of digits in Postgres

However when I apply this to the table I get

|     size      |         x         |
|---------------|-------------------|
| 2880129015916 | 2,880,129,015,916 |
|          2560 | 2,56              |
|             0 |                   |
|      91570752 | 91,570,752        |
|          8192 | 8,192             |
|      21037156 | 21,037,156        |

My query was

 SELECT v99.size,
    rtrim(ltrim(v99.size::money::character varying::text, '$'::text), '.00'::text) AS x
   FROM v99;

Any ideas why 2560 was shown as 2,56 and 0 was shown as ”?
Thanks.

Tags:

2

Answers


  1. 0

    You can use like this:

    select size, to_char(size,'FM99,999,999,999,999,999,999') from myTable;

    DBFiddle demo

    EDIT: BTW, why your trick is faulty and not working?

    rtrim(ltrim(v99.size::money::character varying::text, '$'::text), '.00'::text)

    You are converting size to money, which makes it like:

    v
    $2,880,129,015,916.00
    $2,560.00

    Then you convert it to character varying and then to text (which is redundant and the same thing, done twice).

    Then you left trim the characters ‘$’ (which is already text and casting is unnecessary). They become like:

    v
    2,880,129,015,916.00
    2,560.00

    Then you do a right trim this time to "trim characters" ‘.00’ meaning you are trimming all ‘0’ and ‘.’ characters from right leaving:

    v
    2,880,129,015,916
    2,56

    Hope this makes it clear why it was not working. Simply use the formatting I showed.

    Login or Signup to reply.
  2. 0

    data

    create table mytable (size numeric(20,0));
insert into mytable 
(size) values 
(2880129015916),
(2560),
(0),
(91570752),
(8192),
(21037156);

    use following query

    select  size,replace(replace(round(sum(size), 0)::money::varchar, '$', ''),'.00','') 
from mytable 
group by size

    dbfiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search