Periodic Table Database - add cloumn error

Backend Development
1

Hi,
I am trying to add a column as per:

Your properties table should have a type_id foreign key column that references the type_id column from the types table. It should be an INT with the NOT NULL constraint

periodic_table=> ALTER TABLE properties ADD COLUMN type_id INT NOT NULL;
ERROR:  column "type_id" contains null values
periodic_table=> SELECT * FROM properties;
 atomic_number |   type    | atomic_mass | melting_point_celsius | boiling_point_celsius 
---------------+-----------+-------------+-----------------------+-----------------------
             1 | nonmetal  |    1.008000 |                -259.1 |                -252.9
             2 | nonmetal  |    4.002600 |                -272.2 |                  -269
             3 | metal     |    6.940000 |                180.54 |                  1342
             4 | metal     |    9.012200 |                  1287 |                  2470
             5 | metalloid |   10.810000 |                  2075 |                  4000
             6 | nonmetal  |   12.011000 |                  3550 |                  4027
             7 | nonmetal  |   14.007000 |                -210.1 |                -195.8
             8 | nonmetal  |   15.999000 |                  -218 |                  -183
          1000 | metalloid |    1.000000 |                    10 |                   100
(9 rows)

periodic_table=> ALTER TABLE properties ADD COLUMN type_id INT NOT NULL;
ERROR:  column "type_id" contains null values
periodic_table=>

getting this error?

2

Do you already have a column called type_id?

Double check by running \l properties

3

no, it didn’t

periodic_table=> \d properties;
                           Table "public.properties"
        Column         |         Type          | Collation | Nullable | Default 
-----------------------+-----------------------+-----------+----------+---------
 atomic_number         | integer               |           | not null | 
 type                  | character varying(30) |           |          | 
 atomic_mass           | numeric(9,6)          |           | not null | 
 melting_point_celsius | numeric               |           | not null | 
 boiling_point_celsius | numeric               |           | not null | 
Indexes:
    "properties_pkey" PRIMARY KEY, btree (atomic_number)
    "properties_atomic_number_key" UNIQUE CONSTRAINT, btree (atomic_number)
Foreign-key constraints:
    "properties_atomic_number_fkey" FOREIGN KEY (atomic_number) REFERENCES elements(atomic_number)

Your properties table should have a type_id foreign key column that references the type_id column from the types table. It should be an INT with the NOT NULL constraint

therefore I was trying to add a column to this properties table.

4

Ah I think I get it. You are trying to add a column that is supposed to be not null but you already have data in the table so that is contradictory (because the column will contain null when it gets created).

Instead, either drop all the rows then add the column or,
Add the column without the constraint, fill in all the values then make it not null constraint.

Hope this helps

5

yes that’s right, I dropped all the rows and refilled the data as the other option you mentioned did not work. thanks for help.

1 Like
6

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