Question 114723 in PostgreSQL Question posted in
The official documentation can be found here.

Malformed Array Literal in PostgreSQL when inserting JSON Array from a CSV

I’m trying to insert a JSON Array (defined as "_json" in the table structure) to a PostgreSQL table, using a csv. This is the insert statement:

INSERT INTO "MySchema".my_table (id, main_array) values(DEFAULT, '{{"KeyA": 10,"KeyB":20},{"KeyA": 100,"KeyB":200}}');

Getting the following error:

ERROR: malformed array literal 
Detail: Unexpected array element.

Note that I generate the INSERT statement from the database itself. The node.js Sequelize code is working fine with JSON array.

Here’s the csv file structure:

mytable.csv

1,{{"KeyA": 10,"KeyB":20},{"KeyA": 100,"KeyB":200}}
Tags:

2

Answers


  1. 0

    The correct INSERT statement is:

    INSERT INTO "MySchema".my_table (id, main_array)
values (
   DEFAULT,
   '{"{"KeyA": 10,"KeyB":20}","{"KeyA": 100,"KeyB":200}"}'
);

    The array elements are enclosed in double quotes and the double quotes in them are escaped with backslashes.

    The whole thing would look much simpler if you used a JSON array instead of a PostgreSQL array:

    INSERT INTO "MySchema".my_table (id, main_array)
values (
   DEFAULT,
   '[{"KeyA": 10, "KeyB": 20}, {"KeyA": 100, "KeyB": 200}]'
);
    Login or Signup to reply.
  2. 0

    Just another way to escape simiar to Laurenz please let me know if it helps

    [  {    "KeyA":10,    "KeyB":20  },  {  "KeyA":100,     "KeyB":200   }]
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search