Build A Periodic Table Database Help needed

Hi All,

PROJECT 4 Build A Periodic Table Database

  • You should remove all the trailing zeros after the decimals from each row of the atomic_mass column. You may need to adjust a data type to DECIMAL for this. Be careful not to change the value

I just came to a point which includes removing trailing zeros, but everytime I try to change datatype or else it just doesn’t work. Either I loose value or not working at all even if it works data still shown like this: I even tried the INSERT method still same.

atomic_mass

  1.0080
  4.0026
  6.9400
  9.0122
 10.8100
 12.0110

Needed values for properties(atomic_mass) are like down below right?
(No trailing zeros)

atomic_mass

  1.008
  4.0026
  6.94
  9.0122
 10.81
 12.011
 14.007
 15.9990
  1

Because I looked many places… :smiley:
Tried these:

UPT ... SET ... atomic_mass= CAST(fn...)

or with —> UPT... ::numeric version which gave me no errors when inputting

UPDATE ...  TRUNC(atomic_mass,SCALE(atomic_mass))
ALTER TABLE ... ALTER COLUMN ... SET TYPE DECIMAL(precision,scale) .
                                      ....NUMERIC(precision,scale)

Many many more …
Then I also needed to create a bash script for the broken properties table truncating old data then enter recovery data again and again… :smiley: :joy:

By the way, I also checked the documentation for this with math functions
also there were a code trim_scale(..I dont know its usage.., its not recognized by the v12.9 PSQL) works above v13 Psql but version in VM psql 12.9.

Here is a website which they discussed about this problem:

Am I missing something?

Is this for an FCC (freecodecamp) project or something else? (If fcc, can you post a link to the problems description for my information?) You don’t mention what programming language you want to use also? Other thoughts: can you treat the decimal as a string so you can easily trim the trailing zeros with a regular expression?

1 Like

Freecodecamp Relational Databases (Beta) sertification which BASH(.sh) files and PSQL database is used;

4.th project link:
Relational Databases P4 - Build A Periodic Table Database Project

I could not be able to explain myself very well
But I just couldn’t pass this part of removing trailing zeros. What is needed?

  • They want rounded integer parts like if 1.00080 => 1
  • or trailing zeros 1.00080 => 1.0008 (This is what I am trying to accomplish but no luck so far and need help)

many searches on many websites

In sql when you want to update databases you need to use their commands
which is like;

PSQL command:

UPDATE properties SET atomic_mass = TRUNC(atomic_mass, SCALE(atomic_mass));

In shell you can use it like $($PSQL “UPDATE … …atomic_mass)”)

Which SCALE() gets the variable fractional part for using it UPDATE ING the table ROW value. Like if a number is TRUNC(1.0080, SCALE(1.0080)) how many needed to delete from value SCALE(1.0080) should return 3 => TRUNC(1.0080, 3) => returns VALUE = 1.008 , but scale returns 4.

But even using TYPE of NUMERIC(precision, scale) which helps to define how many fractional part you needed.
If left empty [without paranthesis expressions] it should auto align itself to data.
I’m not sure about it but;
Otherwise it just gets the table definition which NUMERIC(9,4) so fraction variable is 4 but this is not so important. More important point is deleting traling zeros.

Thanks for your attention I found the solution
UPDATE properties SET atomic_mass = atomic_mass::REAL
solved my problem. Yesterday I was so tired to do more, now I see. :slight_smile:

1 Like

Please share where you found this solution, I am stuck on the same step and haven’t came across this in the previous lessons.

I tried your solution but I still have the trailing zeros. I updated the data TYPE to DECIMAL (9) and all the number got rounded up.

Which shows examples using PSQL: TYPE CAST operator of ::;
But maybe this website is not enough so you should focus on type cast operator.

This is the solution which you enter this command to PSQL terminal when you connected to your database.

UPDATE properties SET atomic_mass = atomic_mass::REAL

By the way did you backup the tables using pg_dump?

If you did not then you can use create a .sh file and use this insertagain.sh file to recover back your floating points in your data.

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

VALUES=(
"1,'nonmetal',1.008,-259.1,-252.9,1"
"2,'nonmetal',4.0026,-272.2,-269,1"
"3,'metal',6.94,180.54,1342,2"
"4,'metal',9.0122,1287,2470,2"
"5,'metalloid',10.81,2075,4000,3"
"6,'nonmetal',12.011,3550,4027,1"
"7,'nonmetal',14.007,-210.1,-195.8,1"
"8,'nonmetal',15.999,-218,-183,1"
"1000,'metalloid',1,10,100,3"
)

INSERT(){
  $($PSQL "INSERT INTO properties(atomic_number,type,atomic_mass,melting_point_celsius,boiling_point_celsius,type_id) VALUES($1)")
}
for i in ${!VALUES[@]}
do
  echo ${VALUES[i]}
  INSERT ${VALUES[i]}
  # echo ${VALUES[i]} | while IFS="," read ATOMIC_NUMBER TYPE ATOMIC_MASS MELTING_POINT BOILING_POINT TYPE_ID
  #  do
  #   echo $ATOMIC_MASS
  #   INSERT $ATOMIC_NUMBER $TYPE $ATOMIC_MASS $MELTING_POINT $BOILING_POINT $TYPE_ID
  #  done
done
1 Like

You might need to use DELETE FROM table_name to delete all your rows if insertion fails. You probably know so use this with caution.

Thanks, I’ll read through the link you sent. I’ve my data dumped and saved. This part has me scratching my head a bit.

1 Like