BUG #9776: well formed json with embedd colon ':' causes error on copy

From: larry(dot)price(at)enterprisedb(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #9776: well formed json with embedd colon ':' causes error on copy
Date: 2014-03-29 22:41:45
Message-ID: 20140329224145.342.43172@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 9776
Logged by: Larry Price
Email address: larry(dot)price(at)enterprisedb(dot)com
PostgreSQL version: 9.3.3
Operating system: CENTOS
Description:

using a simple table with json datatype

Table "test.testset"
Column | Type | Modifiers
--------+------+-----------
tune | json |

attempt to insert the following json (well formed according to jq, python
simplejson and other parsers

---
{
"title": "Rinaldo : Act 1 \"Cor ingrato, ti rammembri\" [Rinaldo]",
"track_id": "TRBBIYX128F1497FF2",
"tags": [],
"similars": [],
"timestamp": "2011-09-14 11:38:47.924559",
"artist": "Marilyn Horne"
}
___

attempting to insert using psql \copy

results:

psql -U test -d test -c '\copy testset (tune) from stdin'
ERROR: invalid input syntax for type json
DETAIL: Token "Cor" is invalid.
CONTEXT: JSON data, line 1: ...BBIYX128F1497FF2", "title": "Rinaldo : Act 1
"Cor...
COPY testset, line 1, column tune: "{"artist": "Marilyn Horne", "timestamp":
"2011-09-14 11:38:47.924559", "similars": [], "tags": [], "..."

it seems to be stripping the escape sequences before the json is parsed.
Could be user error. But it appears that strings are not getting escaped
properly before being parsed as json, but only when the copy statement is
used. Other json from the same set gets properly inserted.

using insert with the same value works
---
insert into testset (tune) values ('{
"title": "Rinaldo : Act 1 \"Cor ingrato, ti rammembri\" [Rinaldo]",
"track_id": "TRBBIYX128F1497FF2",
"tags": [],
"similars": [],
"timestamp": "2011-09-14 11:38:47.924559",
"artist": "Marilyn Horne"
}' );
---

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message info 2014-03-30 19:58:33 BUG #9793: to_number wrong convertion
Previous Message Craig Ringer 2014-03-29 13:04:30 Re: Re: BUG #9611: Current jdbc driver doesn't support any classes in Java 8 java.time