How to solve problems with the float type of a column in sql?

Question

I have the following table (name: table_example) in a database on postgres:

      id     number_sensor       
     0001          1              
     0002          2              
     0003          8              
     0004          4              
     0005          1              

I need to create a new column that receives the result of a calculation using the column "number_Sensor". So, I created a new column, named "results" in the table. Defined as the float type.

        ALTER TABLE table_example
        ADD result float(10)

The addition of the new column was done perfectly. As below:

      id     number_sensor       result
     0001          1              [null]
     0002          2              [null]
     0003          8              [null]
     0004          4              [null]
     0005          1              [null]

However, when I do the necessary calculation:

        UPDATE table_example
        SET result = (number_sensor / 4)

I get the following values ​​in the "result" column:

       id     number_sensor       result
     0001          1              0
     0002          2              0
     0003          8              2
     0004          4              1
     0005          1              0

However, I would like the output to be as follows:

      id     number_sensor       result
     0001          1              0.25
     0002          2              0.50
     0003          8              2
     0004          4              1
     0005          1              0.25

Answer 1

I would recommend that you do not persist that column. Rather, calculate it whenever you query the table:

SELECT id, number_sensor,
       number_sensor / 4.o  -- avoid integer division
FROM table_example;

If you need that often, create a view for that query.

Answer 2

Postgres does integer division. Just throw in a decimal place:

UPDATE table_example
    SET result = (number_sensor / 4.0);

Or convert to a float if you prefer that method:

UPDATE table_example
    SET result = (number_sensor::float / 4);