CODEWITHSUNDEEP
postgresql
jhon.smith
jhon.smith

Reputation: 2043

postgresql load a csv with a column having json data

I have a csv file with one column having json data.

source,raw_json
"stackoverflow","{'abcd':'xyz'}"

The column raw_json of the csv file has json data. The table DDL is

create table temp (
source varchar null,
raw_json jsonb null
)

I attempt to load this into postgresql using copy command but I am having issues with loading this data.

COPY temp(source,raw_json)
FROM 'D:\\temp.csv'
(FORMAT CSV, HEADER TRUE, DELIMITER ',', ENCODING 'UTF8', QUOTE '"');

And I run into the below error.

ERROR:  invalid input syntax for type json
DETAIL:  Token "'" is invalid.
CONTEXT:  JSON data, line 1: {'...
COPY temp, line 2, column raw_json: "{'abcd':'xyz'}"

Any idea how I can load the table from the csv file having a json column.

Upvotes: 0

Views: 327

Answers (1)

jjanes
jjanes

Reputation: 44137

Create a temp table where that column is of type text rather than jsonb, and load the data there with your current COPY. Then replace single quotes with double quotes and cast to JSONB and insert into the real target table.

insert into temp select source, replace(raw_json,'''','"')::jsonb from really_raw;

This depends on there being no literal single quotes that were escaped somehow. It also depends on whoever made the so-called JSON in the first place only screwing them up in exactly one way.

Upvotes: 0

Related Questions