From: tao tony [mailto:tonytao0505@xxxxxxxxxxx] 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 ====================================== 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 |
