Solved: How do I perform increment percentage calculations in SQL INSERT or UPDATE statement in PostgreSQL?

Question

I have a product table and I need to update the amount in the column table (float4 datatype) based on the increment percentage column. Example SQL command as below:

CREATE TABLE public."Product" (id int4 NOT NULL DEFAULT, "incrementPercentage" float4 NULL, "amount" float4 NULL, ...);

The incrementPercentage column might have a value or it might be empty (null), I like to know if the UPDATE statement with a CASE statement will work best. I would like to limit the value to 2 decimal places as well.

UPDATE Product
SET amount = CASE WHEN incrementPercentage IS NOT NULL THEN ((incrementPercentage/100)*incrementPercentage + amount) ELSE amount END

Answer

Refer to PostgreSQL 14 documentation related to Data Types, float4 is an alias for real. You might want to check to this statement in Arbitrary Precision Numbers portion:

The data types real and double precision are inexact, variable-precision numeric types.

You’ll need to go with numeric. With numeric you can also specify your precision. With real, I believe you’ll need to take care of precision yourself.