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

Question

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

Your CSV line consists of 5 entries. The first is 1, the second is {{"KeyA": 10, and so on. That second entry is clearly not a valid array. You will have to escape something, because as it is, your CSV is corrupt. — Laurenz Albe
– Laurenz Albe, Commented Sep 9, 2022 at 10:00

Laurenz Albe · Accepted Answer · 2022-09-09 14:02:56Z

1

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

edited Sep 9, 2022 at 14:02

answered Sep 9, 2022 at 12:13

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

4 Comments

sofa_maniac Over a year ago

I want the resulting array in DB to be like: [{"KeyA": 10,"KeyB":20}]. And, this format is giving: malformed array literal. "[" must introduce explicitly-specified array dimensions.

Laurenz Albe Over a year ago

Ah, silly me. I missed that the type you are using is json[] rather than json.

sofa_maniac Over a year ago

Still not working.

Laurenz Albe Over a year ago

Another mistake of mine: You are not loading the CSV file with COPY, you are actually running INSERT.

Robert Ellis · Accepted Answer · 2022-09-09 12:26:01Z

0

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

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

answered Sep 9, 2022 at 12:26

Robert Ellis

7346 silver badges20 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related