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

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

CodePudding user response：

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

[  {    \"KeyA\":10,    \"KeyB\":20  },  {  \"KeyA\":100,     \"KeyB\":200   }]

CodePudding user response：

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}]'
);