How to update new empty column with data that depends on mathematical operation of different data types?

Question

[beginner]

I have a table that looks like this:

colA    colB
1       <null>
2       <null>
3       <null>

colB is the new empty column I added to the table. colA is varchar and colB is double precision data type (float).

I want to update colB with a colA multiplied by 2.

New table should look like this:

colA    colB
1       2
2       4
3       6

When I go to update colB like so:

update tablename set colB = colA * 2

I get error:

Invalid operation: Invalid input syntax for type numeric

Ive tried to work around this with solutions like this:

update tablename set colB = COALESCE(colA::numeric::text,'') * 2

but get the same error.

In a select statement on the same table, this works on colA which is varchar:

select colA * 2 from tablename

How can I update a column with mathematical operations with different datatype reference columns? I cant update datatype for colA.

I would change title of question to: "How to **update** new empty column with data that depends on mathematical operation of different data types?". Or something similar but not **insert** — Alex Yu, Sep 22 '21 at 06:56

Alex Yu · Accepted Answer · 2021-09-22T06:43:42.320

2

I suppose that Laurenz Albe is correct and there are non-numeric values in col_a

So UPDATE must be guarded:

UPDATE T
SET col_b = 
  CASE
     WHEN col_a ~'^([0-9]+\.?[0-9]*|\.[0-9]+)$' THEN col_a::numeric *2
  END ;
-- or this way
UPDATE T
SET col_b = col_a::numeric *2
WHERE
      col_a ~'^([0-9]+\.?[0-9]*|\.[0-9]+)$' ;

Look at fiddle: https://www.db-fiddle.com/f/4wFynf9WiEuiE499XMcsCT/1

Recipes for "isnumeric" you can get here: isnumeric() with PostgreSQL

edited Sep 22 '21 at 06:43

answered Sep 22 '21 at 06:34

Alex Yu

3,412
1
25
38

thank you so much. I think i found the issue. colA has a empty strings, how do I get around this in the update command? Does your update statement take care of empty strings? – RustyShackleford Sep 22 '21 at 15:19
@RustyShackleford Look on db-fiddle: https://www.db-fiddle.com/f/4wFynf9WiEuiE499XMcsCT/4. I added inserts of NULL and empty strings – Alex Yu Sep 22 '21 at 15:35
amazing this workeD! – RustyShackleford Sep 22 '21 at 15:37

score 1 · Answer 2 · answered Sep 22 '21 at 06:10

1

There is a value in the string column that is not a valid number. You will have to fix the data or exclude certain rows with a WHERE condition.

If you say that running the query from your client works, that leads me to suspect that your client doesn't actually execute the whole query, but slaps a LIMIT on it (some client tools do that).

The following query will have to process all rows and should fail:

SELECT colA * 2 AS double
FROM tablename
ORDER BY double;

answered Sep 22 '21 at 06:10

Laurenz Albe

209,280
17
206
263

Maybe `SELECT * FROM tablename WHERE NOT col_a ~~'^([0-9]+\.?[0-9]*|\.[0-9]+)$' ` to check existence of non-numeric values ? – Alex Yu Sep 22 '21 at 06:45
@AlexYu Numbers can be negative, and they can be in scientific notation. – Laurenz Albe Sep 22 '21 at 07:10
Ah. Yes. For that I would use something like this: https://stackoverflow.com/a/16206123/1168212 – Alex Yu Sep 22 '21 at 07:44
@LaurenzAlbe I think found the issue with AlexYu line. There are empty strings in colA, but I am not sure how to exclude them in the update command? – RustyShackleford Sep 22 '21 at 15:18
I accepted @AlexYu answer, but thank you for your help in figuring out the problem. – RustyShackleford Sep 22 '21 at 15:37
@RustyShackleford My answer is the same as Laurenz Albe. It's just easier to understand for novices in SQL – Alex Yu Sep 22 '21 at 15:49

score 0 · Answer 3 · answered Sep 21 '21 at 23:01

0

update tablename set colB = colA::numeric * 2

answered Sep 21 '21 at 23:01

Sergey Zaykov

523
2
9

I get error: `Invalid operation: Invalid input syntax for type numeric` – RustyShackleford Sep 21 '21 at 23:05
Sorry: ```sql update tablename set colB = colA::integer * 2 ``` – Sergey Zaykov Sep 21 '21 at 23:20
You must have a value in colA that is not a number. – Jeremy Sep 21 '21 at 23:21
1

@Jeremy I ran this `select * from table where colA like '%[^a-zA-Z]%'` but no results back. – RustyShackleford Sep 21 '21 at 23:25
@СергейЗайков the integer change also didnt work. – RustyShackleford Sep 21 '21 at 23:26
@Jeremy i did find some rows where `colA` is null, not sure if that makes a difference – RustyShackleford Sep 21 '21 at 23:29
also tried adding `where colA is not null`, but all didnt work. Same error. – RustyShackleford Sep 21 '21 at 23:44
null wouldn't matter, but an empty string would give you an error – Jeremy Sep 22 '21 at 14:03
@Jeremy I think i found the issue but not sure how to fix. I found some rows in colA with empty strings. How do I get around this in the update? – RustyShackleford Sep 22 '21 at 15:17

How to update new empty column with data that depends on mathematical operation of different data types?

3 Answers3