从jsonb成员更新整数列失败并显示:列的类型为integer，但表达式的类型为jsonb [英] Updating integer column from jsonb member fails with: column is of type integer but expression is of type jsonb

问题描述

在PostgreSQL 9.5表中，我有一个integer列social.

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

当我尝试在类型为jsonb的in_users变量中提供以下JSON数据(具有2个对象的数组，每个对象都有一个社交"键)的存储过程中对其进行更新时:

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

然后以下代码失败:

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

，并显示错误消息:

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,

但是我得到了错误:

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

为什么PostgreSQL无法识别数据为integer?

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

从 JSON-TYPE- MAPPING-TABLE 我给人的印象是JSON数字将自动转换为PostgreSQL数字类型.

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

推荐答案

单个基于集合的SQL命令比循环要有效得多:

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?

要回答您的原始问题:

为什么PostgreSQL无法识别数据是整数?

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

因为您试图将jsonb值转换为integer.在您的解决方案中，您已经发现需要->>运算符而不是->来提取text，该运算符可以转换为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

t->'social'::int

除上述内容外:运算符优先级.强制转换运算符::的绑定比json运算符->更强.就像您已经发现自己一样，您真的想要:

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

dba.SE上的情况非常类似:

Very similar case on dba.SE:

• 在PostgreSQL中查询JSONB

这篇关于从jsonb成员更新整数列失败并显示:列的类型为integer，但表达式的类型为jsonb的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！