[SOLVED] How to Update decimal data in db with psql from the bash script

Issue

1. I have a table with numeric type data: numeric(9,6).
2. I want to change this data (get rid of trailing zeros) in the bash script and update it in the db.

But it looks like after manipulations in bash data should be converted to decimal again.

My .sh script:

#!/bin/bash
PSQL="psql -X --username=****** --dbname=periodic_table --tuples-only -c";

echo -e "\n~~~~~ Remove trailing zeros & update db ~~~~~\n"

# get atomic_mass
ELEMENTS=$($PSQL "SELECT atomic_number, atomic_mass FROM properties")
echo "$ELEMENTS" | while read ATOMIC_NUMBER BAR ATOMIC_MASS
do
  #echo "$ATOMIC_NUMBER | $ATOMIC_MASS"
  # remove trailing zeros
  ATOMIC_MASS_CLEAN=$(echo $ATOMIC_MASS | sed -e "s/0*\s*$//" | sed -e "s/\.$//")
  echo "$ATOMIC_NUMBER | $ATOMIC_MASS | $ATOMIC_MASS_CLEAN"
  # Update db
  UPDATE_DB=$($PSQL "UPDATE properties SET atomic_mass = $ATOMIC_MASS_CLEAN WHERE atomic_mass = $ATOMIC_MASS")
  echo $UPDATE_DB
done

Result

$ ./remove_trailing_zeros.sh 

~~~~~ Remove trailing zeros & update db ~~~~~

3 | 6.940000 | 6.94
UPDATE 1
4 | 9.012200 | 9.0122
UPDATE 1
1 | 1.008000 | 1.008
UPDATE 1
2 | 4.002600 | 4.0026
UPDATE 1
6 | 12.011000 | 12.011
UPDATE 1
7 | 14.007000 | 14.007
UPDATE 1
8 | 15.999000 | 15.999
UPDATE 1
5 | 10.810000 | 10.81
UPDATE 1
1000 | 1.000000 | 1
UPDATE 1

But it looks like $ATOMIC_MASS_CLEAN can't be simply converted to Numeric data :( The script is running without errors but nothing happens in the db. Everything works fine with INT data and VARCHAR but not with Numeric. Help to fix it, please.

Solution

Not so much an answer as a demonstration

create table numeric_test(num_fld numeric, num6_fld numeric(9,6));

insert into numeric_test values (2, 2), (2.100, 2.100), (2.000, 2.000);

 select * from numeric_test ;
 num_fld | num6_fld 
---------+----------
       2 | 2.000000
   2.100 | 2.100000
   2.000 | 2.000000

When you create a numeric field with an explicit scale, the 6, then you will get trailing zeros out to that scale. Without an explicit scale then the trailing zeros entered will be preserved.

Answered By - Adrian Klaver

Answer Checked By - Robin (WPSolving Admin)