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