Обсуждение: BUG #9776: well formed json with embedd colon ':' causes error on copy
BUG #9776: well formed json with embedd colon ':' causes error on copy
От
larry.price@enterprisedb.com
Дата:
The following bug has been logged on the website: Bug reference: 9776 Logged by: Larry Price Email address: larry.price@enterprisedb.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" }' ); ---
larry.price@enterprisedb.com writes: > [ backslashes disappearing from an input value ] > 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. "User error" sounds about right to me. What is producing the file you are feeding to \copy, and is it familiar with the quoting rules for COPY input? In particular, the need for backslashes to be doubled? See File Formats / Text Format, about halfway down this page: http://www.postgresql.org/docs/9.3/static/sql-copy.html regards, tom lane