Updating integer column from jsonb member fails wi

问题:

In a PostgreSQL 9.5 table I have an integer column social.

When I try to update it in a stored procedure given the following JSON data (an array with 2 objects, each having a "social" key) in the 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 the following code is failing:

    FOR t IN SELECT * FROM JSONB_ARRAY_ELEMENTS(in_users)
    LOOP
            UPDATE words_social SET
                    social = t->'social',
            WHERE sid = t->>'sid';
    END LOOP;

with the error message:

ERROR:  column "social" is of type integer but expression is of type jsonb
LINE 3:                         social = t->'social',
                                         ^
HINT:  You will need to rewrite or cast the expression.

I have tried changing that line to:

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

but then I get the error:

ERROR:  invalid input syntax for integer: "social"
LINE 3:                         social = t->'social'::int,
                                            ^

Why doesn't PostgreSQL recognize that the data is integer?

From the JSON-TYPE-MAPPING-TABLE I was having the impression that JSON number would be auto-converted to PostgreSQL numeric type.

回答1:

A single set-based SQL command is far more efficient than looping:

UPDATE words_social w
SET    social = (iu->>'social')::int
FROM   JSONB_ARRAY_ELEMENTS(in_users) iu  -- in_user = function variable
WHERE  w.sid = iu->>'sid';                -- type of sid?

To answer your original question:

Why doesn't PostgreSQL recognize that the data is integer?

Because you were trying to convert the jsonb value to integer. In your solution you already found that you need the ->> operator instead of -> to extract text, which can be cast to integer.

Your second attempt added a second error:

t->'social'::int

In addition to the above: operator precedence. The cast operator :: binds stronger than the json operator ->. Like you found yourself already, you really want:

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

Very similar case on dba.SE:

• Querying JSONB in PostgreSQL

回答2:

I've ended up using:

FOR t IN SELECT * FROM JSONB_ARRAY_ELEMENTS(in_users)
LOOP
        UPDATE words_social SET
                social = (t->>'social')::int
        WHERE sid = t->>'sid';

        IF NOT FOUND THEN
                INSERT INTO words_social (social)
                VALUES ((t->>'social')::int);
        END IF;
END LOOP;