CODEWITHSUNDEEP
arrayspostgresql
precose
precose

Reputation: 614

Updating an array and converting from varchar to integer

I'm updating an array by adding a varchar to an int4 array. The 'varchar' field is all numbers so I tried casting it to ::integer but it isn't working

update dwh.attr_lookup set kli_tree = array[target_909_kli::integer] || kli_tree

is giving me this error 

ERROR: ERROR: invalid input syntax for integer: ""

Query = update
dwh.attr_lookup set kli_tree = array[target_909_kli::integer]
|| kli_tree

What is the proper way to do this?

Upvotes: 0

Views: 447

Answers (2)

mu is too short
mu is too short

Reputation: 434685

You're trying to cast an empty string to an integer and that doesn't work:

=> select ''::int;
ERROR:  invalid input syntax for integer: ""
LINE 1: select ''::int;
               ^

You'll have to decide what you want to do with empty strings. If you want to convert them to zeros, then something like this should work:

array[case when target_909_kli = '' then 0 else target_909_kli::integer end]

Upvotes: 2

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656872

Obviously your varchar field is not all numbers. There would not be any double quotes in this case - as the error message informs us.

Try giving a full example if the error has not clear by now. Including table definition and sample values.

Upvotes: 1

Related Questions