How can I import a CSV file containing JSON into postgresql?

Question

I am trying to import this CSV file:

HEADER
"{
  ""field1"": ""123"",
  ""field2"": ""456""
}"
"{
  ""field1"": ""789"",
  ""field2"": ""101""
}"

into my Postgres table.

However, it seems that the \copy my_table(my_text) from 'my_file.csv' command is creating a row for each line of the file.

This is what I get :

                       my_text
-----------------------------------------------------
 HEADER
 "{
   ""field1"": ""123"",
   ""field2"": ""456""
 }"
 "{
   ""field1"": ""789"",
   ""field2"": ""101""
 }"
(9 rows)

and what I expect:

{""field1"": ""123"", ""field2"": ""456""}
{""field1"": ""789"", ""field2"": ""101""}
(2 rows)

Jim Jones · Accepted Answer · 2021-07-09 12:52:53Z

1

Escaping the new line might do the trick:

\copy my_table(my_text) from my_file.csv csv header escape E'\n' quote '"'

edited Jul 9, 2021 at 12:52

answered Jul 9, 2021 at 12:40

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

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

2 Comments

maxime.b28 Over a year ago

using this method I get 2 rows with a '{' as a value.

Jim Jones Over a year ago

are you sure? keep in mind that the new line is part of the string, so if you're visualizing the result set with a tool like pgadmin you might need to "resize" the row to see the whole thing. In my machine it works just fine @JamieA

jjanes · Accepted Answer · 2021-07-09 13:39:50Z

0

The default for \copy is "text" format, not "csv" format. All you have to do is tell your \copy to use csv, and that there is a header line.

\copy my_table(my_text) from 'my_file.csv' csv header

Changing the escape character as the other answer suggests is unnecessary and in fact will break things. Your data will load, but will not be valid JSON.

You probably want to make this column type JSON or JSONB, not text. This will automatically validate your data as being valid JSON data, and in the case of JSONB will make future parsing of it faster.

answered Jul 9, 2021 at 13:39

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Collectives™ on Stack Overflow

How can I import a CSV file containing JSON into postgresql?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related