json - Updating integer column from jsonb member fails with: column is of type integer but expression is of type jsonb -


in postgresql 9.5 table have integer column social.

when try update in stored procedure given following json data (an array 2 objects, each having "social" key) in in_users variable of type jsonb:

'[{"sid":"12345284239407942","auth":"ddddc1808197a1161bc22dc307accccc",**"social":3**,"given":"alexander1","family":"farber","photo":"https:\/\/graph.facebook.com\/1015428423940942\/picture?type=large","place":"bochum, germany","female":0,"stamp":1450102770},   {"sid":"54321284239407942","auth":"ddddc1808197a1161bc22dc307abbbbb",**"social":4**,"given":"alxander2","family":"farber","photo":null,"place":"bochum, germany","female":0,"stamp":1450102800}]'::jsonb

then following code failing:

    t in select * jsonb_array_elements(in_users)     loop             update words_social set                     social = t->'social',             sid = t->>'sid';     end loop;

with error message:

error:  column "social" of type integer expression of type jsonb line 3:                         social = t->'social',                                          ^ hint:  need rewrite or cast expression.

i have tried changing line to:

social = t->'social'::int,

but error:

error:  invalid input syntax integer: "social" line 3:                         social = t->'social'::int,                                             ^

why doesn't postgresql recognize data integer?

from json-type-mapping-table having impression json number auto-converted postgresql numeric type.

a single set-based sql command far more efficient looping:

update words_social w set    social = (iu->>'social')::int   jsonb_array_elements(in_users) iu  -- in_user = function variable  w.sid = iu->>'sid';                -- type of sid?

to answer original question:

why doesn't postgresql recognize data integer?

because trying convert jsonb value integer. in solution found need ->> operator instead of -> extract text, can cast integer.

your second attempt added second error:

t->'social'::int

in addition above: operator precedence. cast operator :: binds stronger json operator ->. found already, want:

(t->>'social')::int

very similar case on dba.se:


Comments

Post a Comment

Popular posts from this blog

sublimetext3 - what keyboard shortcut is to comment/uncomment for this script tag in sublime -

Image
i have script tag in code, keyboard shortcut comment/uncomment script tag in sublime. after selecting script open , close tag. <script src="http://code.jquery.com/jquery-latest.min.js" type="text/javascript"> </script> i pressing ctrl + shift + / , getting this /*<script src="http://code.jquery.com/jquery-latest.min.js" type="text/javascript"> </script>*/ also pressed ctrl + / , got this // <script src="http://code.jquery.com/jquery-latest.min.js" type="text/javascript"> // </script> instead of <!-- <script src="http://code.jquery.com/jquery-latest.min.js" type="text/javascript"> </script> --> script open , close tag in 2 lines not in 1 line , not have spaces too. for mac,high light lines want comment. hold 'command , / ' commented. for windows: high line or select lines ,then hold ctrl + / if not work: ...
Read more

post - imageshack API cURL -

i credentials correct. when send image through form this:(action="http://api.imageshack.com/v2/images/"). working fine when tried send $ch = curl_init($url); failed. code: enter code here <form action="" method="post" enctype="multipart/form-data"> select image upload: <input type="file" name="file" id="file" multiple> <input type="text" name="auth_token" id="auth_token" value="xxxxxxxxxxxxxxxx"> <input type="text" name="api_key" id="api_key" value="xxxxxxxxxxxxxxx"> <input type="submit" value="upload image" > </form> $url = ' http://api.imageshack.com/v2/images/ '; $temp = $_files["file"]['tmp_name']; echo $temp."<br>" ; $ch = curl_init($url); curl_setopt($ch, curlopt_post, true); curl...
Read more

dataset - MPAndroidchart returning no chart Data available -

i having problems mpandroidchart. adding temperature , humidity data chart, displays "no chart data available". here can see bug is? super grateful! //create data //set size of data int size = list.size(); if (size > 24){ size = 24;} //create lists form temp , hum arraylist<entry> temps = new arraylist<entry>(); arraylist<entry> hums = new arraylist<entry>(); //fill list temp values (int = 0; < size; i++){ entry value = new entry(math.round(list.get(list.size()-size + i).temp), (size-i)); temps.add(value); } //fill list hum values (int = 0; < size; i++){ entry value = new entry(math.round(list.get(list.size()-size + i).hum), (size-i)); hums.add(value); } ...
Read more