invalid input syntax for integer pgsql

Question

I have just started with postgresql. I have a json object in table. There is a numeric value in the json object in which i want to add a number and assign it to other integer. This is how I am doing

declare  
 total_votes integer;
....
select result into poll_result from polls where id = 1;
total_votes =  (select poll_result::json#>'{total_votes}'::integer + 1);

But this is showing

ERROR:  invalid input syntax for integer: "{total_votes}"
LINE 1: SELECT (select poll_result::json#>'{total_votes}'::integer +...

poll_result has the data like

{
    "yes": 1,
    "no": 0,
    "total_votes": 1
}

and when I try to print total_votes using

RAISE NOTICE '%',poll_result::json#>'{total_votes};

It prints 1.

even I have tried

total_votes =  (select (poll_result::json#>'{total_votes}')::integer + 1);

But the error

ERROR:  cannot cast type json to integer
LINE 1: ...ELECT (select (poll_result::json#>'{total_votes}')::integer ...

klin · Accepted Answer · 2018-04-21 14:40:01Z

7

The operator #> gives a json, while #>> gives a text and you need the second one:

select (poll_result::json #>> '{total_votes}')::integer + 1

or

select (poll_result::json ->> 'total_votes')::integer + 1

See JSON Functions and Operators.

answered Apr 21, 2018 at 14:40

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

invalid input syntax for integer pgsql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related