Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

malformed array literal - PostgreSQL

Tags:

sql

postgresql

I want to copy an array from jsonb field to a PostgreSQL array column:

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?

http://sqlfiddle.com/#!17/d8122/2

like image 967
Mateusz Urbański Avatar asked Feb 19 '18 12:02

Mateusz Urbański


People also ask

What is malformed array literal?

Explanation: This error usually occurs when array values get passed to Postgres, but the format / syntax of the array is not correct for Postgres.

How to declare an array in PostgreSQL?

Declaration of Array Types. To illustrate the use of array types, we create this table: CREATE TABLE sal_emp ( name text, pay_by_quarter integer[], schedule text[][] ); As shown, an array data type is named by appending square brackets ( [] ) to the data type name of the array elements.


1 Answers

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:

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 '{}'

as alternative smth like:

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"

like image 101
Vao Tsun Avatar answered Sep 22 '22 11:09

Vao Tsun