RE: copy error with json/jsonb types

From: tao tony [mailto:tonytao0505(at)outlook(dot)com]
Sent: Wednesday, November 29, 2017 10:02 PM

I'm using copy and jdbc copyin to build load data to pg,data type in table
is jsonb.postgresql version are 9.6 and 10.0. Some records with escape
characters would be failed with error"ERROR: invalid input syntax for type
json" but when using insert they could be executed successfully.

please kindly tell me how to fix this issue?

here is the table.

hdb=# \d salejsonb
Table "public.salejsonb"
Column | Type | Collation | Nullable | Default
---------+-------+-----------+----------+---------
jsonval | jsonb | | |

======================================

I may be old school, but it would be rare situation where I'd be using an
ETL process - and I consider the Copy function to be an extract and load
part of that - to go directly into a live/final/production table.

Instead, I "always" use a staging table that is much more tolerant of
errors. In this case, I'd create a table with the one column with a
datatype of "text". Now run the copy command, the data will land without
error, then you can either devise processes that detect and fix invalid json
or do a proper error out from the db, and on receiving proper json,
proceeding to do a guaranteed good insert into your final jsonb table. If
you were able to land the data in that staging table and then render it, you
may be able to find a pattern in the invalid escape characters that you
could fix on the fly on the insert from staging to final.

Mike Sofen