Bulk Update JSon fields in PostgreSQL

Question

We are trying to import tables into PostgreSQL from SQL Server. In source data, there are tables with a JSon column storing nested information for parent record in Master Detail fashion. For example, for one Order, there can be multiple properties stored in JSon Column in the same row.

While this approach worked well for SQL Server, in trying to do the same on PostgreSQL, we are running into an error "ERROR: invalid input syntax for type json" DETAIL: Expected end of input, but found ",".

Has anyone encountered a similar situation and if so, can you suggest the right way to achieve the desired result. You can refer to the following insert statement as example.

INSERT INTO "customer" 
            ("name", 
             "id", 
             "details") 
VALUES     ("abc", 
            1, 
            '{"PhoneNO":"987654321","Gender":"Female"},{"PhoneNO: "98123456"} ')

pifor · Accepted Answer · 2020-05-23 11:52:46Z

1

There is a missing double quote for "PhoneNO and there are missing brackets:

create table "Customer" (
"NAME" varchar,
"ID" int,
"details" json 
);
CREATE TABLE

INSERT INTO "Customer" ("NAME", "ID", "details") VALUES('ABC',1, '[{"PhoneNO":"987654321","Gender":"Female"},{"PhoneNO": "98123456"}]');
INSERT 0 1

And you cannot use "..." for VARCHAR literals: you must use '...'.

edited May 23, 2020 at 11:52

user330315

answered May 22, 2020 at 18:32

pifor

7,9722 gold badges11 silver badges18 bronze badges

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

Collectives™ on Stack Overflow

Bulk Update JSon fields in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related