0

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 – a_horse_with_no_name Oct 10 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 – Chris Jenner Oct 10 at 9:03
2

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. – Chris Jenner Oct 10 at 9:06
0

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.

0

This is what worked for me

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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