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.



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



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))

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; 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:


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 file to recover back your floating points in your data.

PSQL="psql -X --username=freecodecamp --dbname=periodic_table --tuples-only -c"


  $($PSQL "INSERT INTO properties(atomic_number,type,atomic_mass,melting_point_celsius,boiling_point_celsius,type_id) VALUES($1)")
for i in ${!VALUES[@]}
  echo ${VALUES[i]}
  #  do
  #   echo $ATOMIC_MASS
  #  done

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.

1 Like

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

Hi, it does not work for me… i tried UPDATE properties SET atomic_mass = atomic_mass::REAL and some other ideas but nothing works… I get proper result when i do SELECT atomic_mass = atomic_mass::REAL FROM properties, but it does not work when I want to update the column (although I get message UPDATE 9 as if eveything was ok)… have you got any ideas or suggestions? thanks

I’ve got the same problem. I tried different solutions and all of them show that columns were updated, but non of them changes the value.

If you found the solution let me know please, I’m stuck here

1 Like

I found the solution which works for me.

  1. We should set another type from numeric(9,6) to decimal:
    ALTER TABLE properties ALTER COLUMN atomic_mass TYPE DECIMAL;

  2. trim the values:
    UPDATE properties SET atomic_mass=trim(trailing '00' FROM atomic_mass::TEXT)::DECIMAL;


Thank you! Done! Now I also understand what was my main problem. Previously I did ALTER TABLE properties ALTER COLUMN atomic_mass TYPE DECIMAL(9,6); …should be just TYPE DECIMAL…
Now it seems that both solutions work. I tried them both after rebuilding my base form sql file. Thaks again.

Otra forma es usar esto dos veces de esta manera
echo $($PSQL “ALTER TABLE properties ALTER COLUMN atomic_mass TYPE DECIMAL(9,0);”)

echo $($PSQL “ALTER TABLE properties ALTER COLUMN atomic_mass TYPE DECIMAL;”) Aunque es necesario usarlo de forma consecutiva

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.