格式错误的数组文字 - PostgreSQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/48866673/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
malformed array literal - PostgreSQL
提问by Mateusz Urbański
I want to copy an array from jsonb field to a PostgreSQL array column:
我想将一个数组从 jsonb 字段复制到一个 PostgreSQL 数组列：
CREATE TABLE survey_results (
id integer NOT NULL,
areas text[],
raw jsonb DEFAULT '{}'::jsonb
);
INSERT INTO survey_results (id, raw)
VALUES (1, '{"areas": ["test", "test2"]}');
UPDATE survey_results SET areas = CAST(raw#>>'{areas}' AS text[]);
This returns me?
这还给我？
ERROR: malformed array literal: "["test", "test2"]" Detail: "[" must introduce explicitly-specified array dimensions.
How can I fix that?
我该如何解决？
回答by Vao Tsun
http://sqlfiddle.com/#!17/d8122/33
http://sqlfiddle.com/#!17/d8122/33
json array is not self castable to postgres array. You need to either properly parse and cast it (json_array_elements, unnest, array_agg), or use some monkey hack, like:
json 数组不能自我投射到 postgres 数组。您需要正确解析并转换它 ( json_array_elements, unnest, array_agg)，或者使用一些猴子 hack，例如：
UPDATE survey_results
SET areas = concat('{',translate(raw#>>'{areas}','"',$$'$$),'}')::text[];
above I "prepare" json array, by changing quotes, so they would be literal, not identifier ones and prepare it to postgres array text representation '{}'
上面我通过更改引号“准备”json数组，因此它们将是文字，而不是标识符并将其准备为postgres数组文本表示 '{}'
as alternative smth like:
作为替代 smth 像：
with a as (
select jsonb_array_elements_text(raw#>'{areas}') e from survey_results
)
, b as (
select array_agg(e) ag from a
)
UPDATE survey_results
SET areas = ag::text[]
FROM b;
select * from survey_results
;
can be used - for safer "casting"
可以使用 - 更安全的“铸造”
