Update column with the value of two columns multiplied

Question

I want to update ColumnA with the value of ColumnB x ColumnC. Can someone please help? I have tried the following:

UPDATE database.table
SET ColumnA = ColumnB * ColumnC;

Error message as follows:

ERROR: operator does not exist: double precision * character varying LINE 2: SET ColumnA = ColumnB * ColumnC; ^ HINT: No operator matches the given name and argument types. You might need to add explicit type casts. SQL state: 42883 Character: 58

Answer 1

Text values must be converted to numeric before any math operation.

UPDATE schema.table
SET    ColumnA * ColumnC::numeric  -- Use same data type as ColumnA

Have a look at this tutorial

Answer 2

With the latest version of PostgreSQL, Version 12, you can now have computed columns on your tables. This way you define the computed column once during definition time, and then leave it up to PostgreSQL to do the computations thereafter.

Answer 3

This is what worked for me

UPDATE schema.table
   SET ColumnA = ColumnB * cast(ColumnC as double precision);

Answer 4

You can do math inside PostGres, very nice.

UPDATE myschema.users
SET "recipeTrendingScore" = ("recipeTrendingScore" * 0.75) + 1.0;