panic: pq: invalid input syntax for type json

[jz3xyz]

I'm having difficulty using this utility with JSONB fields and have only had success when uploading empty objects {}.

1. {\"a\":1} - invalid
2. {"a":1} - invalid
3. "{\"a\":1}" - invalid
4. "{a:1}" - invalid

Table structure

localhost:5432 app_user@app_db=# \d event;
                               Table "public.event"
   Column   |           Type           | Collation | Nullable |      Default       
------------+--------------------------+-----------+----------+--------------------
 id         | uuid                     |           | not null | uuid_generate_v4()
 dataset_id | uuid                     |           |          | 
 time       | timestamp with time zone |           | not null | 
 nsec       | bigint                   |           | not null | 
 data       | jsonb                    |           | not null | 
 created_at | timestamp with time zone |           | not null | CURRENT_TIMESTAMP
 updated_at | timestamp with time zone |           | not null | CURRENT_TIMESTAMP
Indexes:
    "event_time_idx" btree ("time" DESC)
Triggers:
    ts_insert_blocker BEFORE INSERT ON event FOR EACH ROW EXECUTE PROCEDURE _timescaledb_internal.insert_blocker()
Number of child tables: 1 (Use \d+ to list them.)

csv file

3ca9d25f-d27c-41fe-abf9-ef6e7821a22a,bce5027d-ccbf-437d-8213-f4a3758cc105,2019-04-18T19:48:03.911Z,1555616883911709953,{},2019-04-18T19:48:03.911Z,2019-04-18T19:48:03.911Z
fa58b515-e45a-47e9-aa25-f801fb2a207a,d1e501ec-f918-4854-b457-11eccf7ebf02,2019-04-18T19:48:03.914Z,1555616883914955884,{},2019-04-18T19:48:03.911Z,2019-04-18T19:48:03.911Z
b97a1928-4c9b-4104-83b9-5736f7c288f6,d1e501ec-f918-4854-b457-11eccf7ebf02,2019-04-18T19:48:03.918Z,1555616883918841778,{\"a\":1},2019-04-18T19:48:03.911Z,2019-04-18T19:48:03.911Z
486f10bf-e812-402d-81c0-4d1110ab2e1b,2f866d33-e130-4622-a6af-385ad827d4dd,2019-04-18T19:48:03.919Z,1555616883919281862,[],2019-04-18T19:48:03.911Z,2019-04-18T19:48:03.911Z

Command

~/go/bin/timescaledb-parallel-copy --connection postgres://app_user:password@localhost:5432/app_db?sslmode=disable --table event --file /tmp/para5small.csv

[binakot]

Any progress in fixing this error?

[jchampio]

This utility uses the COPY FROM command to do its work, so in general the incoming CSV needs to follow those conventions:

The values in each record are separated by the DELIMITER character. If the value contains the delimiter character, the QUOTE character, the NULL string, a carriage return, or line feed character, then the whole value is prefixed and suffixed by the QUOTE character, and any occurrence within the value of a QUOTE character or the ESCAPE character is preceded by the escape character.

Your JSON contains the QUOTE character (" by default), so it needs to be surrounded by QUOTEs, and the existing double-quotes need to be escaped with ESCAPE (also " by default, not \).

Try "{""a"":1}".