Removing all the trailing zeroes of a numeric column in PostgreSQL

Question

I have this table properties which has a column atomic_mass of type NUMERIC(9,6):

 atomic_mass 
-------------
    1.008000
    4.002600
    6.940000
    9.012200
   10.810000
   12.011000
   14.007000
   15.999000
    1.000000
(9 rows)

So, I want to remove all the trailing zeros of the column such as 1.008, 4.0026, etc. So, I tried to do the following:

UPDATE properties SET atomic_mass=trim(trailing '0' from atomic_mass::text)::numeric;

But, it's not working. I tested the trim function which works fine. If I type

SELECT trim(trailing '0' from atomic_mass::text)::numeric from properties

it returns

rtrim  
--------
  1.008
 4.0026
   6.94
 9.0122
  10.81
 12.011
 14.007
 15.999
      1

The column that I wanted. So, what is it that I am doing wrong here? I am using PostgreSQL 12.9.

Answer 1

You have defined the column as NUMERIC(9,6). From here Numeric types that is NUMERIC(precision, scale), where scale is :

The scale of a numeric is the count of decimal digits in the fractional part, to the right of the decimal point.

So running trim to update the values is not going to help as the column definition scale will override that. The trailing zeros is a formatting issue that will have to be dealt with on output.

UPDATE

Given the information in your comments to this answer about being able to change the column type:

create table numeric_test (num_fld numeric, num6_fld numeric(9,6));
insert into numeric_test values (12.011000, 12.011000), (4.002600, 4.002600), (1.000000, 1.000000);

select * from numeric_test ;
  num_fld  | num6_fld  
-----------+-----------
 12.011000 | 12.011000
  4.002600 |  4.002600
  1.000000 |  1.000000

update numeric_test set num_fld = trim(trailing '0' from num_fld::text)::numeric, num6_fld = trim(trailing '0' from num6_fld::text)::numeric ;

select * from numeric_test ;
 num_fld | num6_fld  
---------+-----------
  12.011 | 12.011000
  4.0026 |  4.002600
       1 |  1.000000

--
insert into numeric_test values (9.012200, 9.012200);

select * from numeric_test ;
 num_fld  | num6_fld  
----------+-----------
   12.011 | 12.011000
   4.0026 |  4.002600
        1 |  1.000000
 9.012200 |  9.012200

With the unconstrained numeric you can remove trailing zeros from existing values on an update that trims them. However you will still get them if they are included in a insert or update that does not trim them.

Answer 2

After converting to decimal type, you can also type the following:

UPDATE properties SET atomic_mass = atomic_mass::REAL; enter image description here

Hope it helps!

enter image description here