1

I have a file with JSON data in it. The file was given to me "pretty printed" (with indents, etc), but the PostgreSQL \copy fails to load it. It gives the following error:

CONTEXT:  JSON data, line 1: {
COPY test_data, line 1, column data: "{"

This error is easy to reproduce. Create a temp table:

 CREATE TEMP TABLE target(data jsonb);

Next create 2 files. In file1, put the following text:

{
    "catalog_name":"foo","sale_date":"2016-02-09T00:00:00"
}

In file2:

{"catalog_name":"foo","sale_date":"2016-02-09T00:00:00"}

The \copy fails on file1 but succeeds on file2.

According to jsonlint.com they are both valid json strings.

Is it possible to use \copy to load file2 without any modifications to the file?

I'm using PostgreSQL 9.4.5

David S
  • 12,967
  • 12
  • 55
  • 93
  • 1
    I think you have to keep every record in a single line for the default COPY input format: http://stackoverflow.com/questions/24190039/inserting-valid-json-with-copy-into-postgres-table – Thilo Feb 10 '16 at 23:43

1 Answers1

1

If your data is not very sensitive to newlines, you could try stripping them from the JSON input.

For e.g.

cat input.json | tr -d '\n' > input2.json

Using the new input file should work.

Robins Tharakan
  • 2,209
  • 19
  • 17