CODEWITHSUNDEEP
jsonpostgresqlpsqlpostgresql-9.4
David S
David S

Reputation: 13861

Postgresql copy fails on pretty printed JSON

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

Upvotes: 1

Views: 377

Answers (1)

Robins Tharakan
Robins Tharakan

Reputation: 2473

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.

Upvotes: 1

Related Questions