Updating an array and converting from varchar to integer

Question

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?

mu is too short · Accepted Answer · 2012-04-09 22:00:09Z

2

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]

answered Apr 9, 2012 at 22:00

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Erwin Brandstetter · Accepted Answer · 2012-04-09 21:42:23Z

1

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.

edited Apr 9, 2012 at 21:42

answered Apr 9, 2012 at 21:37

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

1 Comment

mu is too short Over a year ago

Actually it means that the OP is trying to cast an empty string, not a string with double quotes.

Collectives™ on Stack Overflow

Updating an array and converting from varchar to integer

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related