facing 'malformed array literal' when trying to insert json in postgres

Question

I'm trying to pass a json object from my python code with psycopg2 like this to a postgres stored procedure:

{
  "experience": null,
  "phoneNumber": [
    "091184xxx"
  ],
  "location": "tehran"
}

but this error occur:

ERROR: malformed array literal: "phoneNumbers" Detail: Array value must start with "{" or dimension information.

how can I fix this error?

UPDATE: here is my stored procedure:

CREATE OR REPLACE FUNCTION data(job_req JSONB)
  RETURNS VOID
AS $$
DECLARE

INSERT INTO "JobRequirements" (expertise,"phoneNumbers", "location")
  VALUES (
    job_req ->> 'expertise',
    job_req ->> 'phoneNumbers' :: VARCHAR [],
    job_req ->> 'location'
  );
END;
$$ LANGUAGE 'plpgsql';

and type of "phoneNumbers" column is varchar(255) []

Show us the insert query that runs from the procedure and the definition of the table being inserted. — Kaushik Nayak
– Kaushik Nayak, Commented Dec 11, 2018 at 7:44
I think you need to do sth. like this. If so, yes, it is lame... — user1602492
– user1602492, Commented Dec 11, 2018 at 10:09

Kaushik Nayak · Accepted Answer · 2018-12-11 10:22:04Z

2

You must extract the JSON array element as a text and convert it to a VARCHAR array before inserting.

CREATE OR REPLACE FUNCTION data(job_req JSONB)
  RETURNS VOID
AS $$
BEGIN
INSERT INTO "JobRequirements" (expertise,"phoneNumbers", "location")
  SELECT 
    job_req ->> 'expertise' ,
     array_agg(d.phonenumber::VARCHAR ) ,
    job_req ->> 'location' 
    FROM jsonb_array_elements_text(job_req->'phoneNumbers') as d(phonenumber);
END;
$$ LANGUAGE plpgsql;

Demo

answered Dec 11, 2018 at 10:22

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

facing 'malformed array literal' when trying to insert json in postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related