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

  • 1
    Why are you storing numbers in a varchar column? That is a really, really bad idea
    – user1822
    Commented Oct 10, 2019 at 8:57
  • @a_horse_with_no_name It's not my dataset. It's a shapefile that i've imported into Postgres through QGIS Commented Oct 10, 2019 at 9:03

4 Answers 4


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

  • Thanks for your help with this - I've added what worked for me below after following your comments. Commented Oct 10, 2019 at 9:06

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.


This is what worked for me

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

You can do math inside PostGres, very nice.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.