Обсуждение: copy error with json/jsonb types

hi dears, 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 | | | below is th sample data: {"uuid":"b93b0307-c71b-4c07-8f29-e490cad3e5f1","version":0,"createInfo":{"time":"2017-11-11T08:59:47.000+0800","operator":{"id":"13832122566","fullName":"马秀菊","qualifiedId":"13832122566"}},"lastModifyInfo":{"time":"2017-11-11T08:59:47.000+0800","operator":{"id":"13832122566","fullName":"马秀菊","qualifiedId":"13832122566"}},"number":"171111-002","shop":"a02a7070","posNo":"1","amount":14.1000,"discountAmount":0.0000,"changeAmount":0.0000,"qty":0.4700,"lines":[{"uuid":"14fdcfe8-6199-4273-91a9-158d719722ae","line":1,"shopSku":"c0229a90-f6c0-41b6-9cd7-a5f85e371b08","shopSkuBarcode":"0000055","platformSku":"","skuName":"蒸蛋糕（散）","skuMunit":"公斤","price":30.0000,"qty":0.4700,"amount":14.1000,"costPrice":18.0000,"saleLine":"d47ea60e-da40-410c-80e6-7716eae2e592"},{"uuid":"c443ef5c-865d-458b-bb0a-af3f11446413","line":2,"shopSku":"c892cc8b-8bec-4fb2-95c3-b2466e09d6fb","shopSkuBarcode":"0000053","platformSku":"","skuName":"达利园派\\蛋糕（散）","skuMunit":"公斤","price":24.0000,"qty":0.0000,"amount":0.0000,"costPrice":16.0000,"saleLine":"1e114c97-83a3-4dce-8655-a17f21aeb39b"},{"uuid":"58be2eab-dd49-4b9e-983f-c17a7d3a4c7e","line":3,"shopSku":"c892cc8b-8bec-4fb2-95c3-b2466e09d6fb","shopSkuBarcode":"0000053","platformSku":"","skuName":"达利园派\\蛋糕（散）","skuMunit":"公斤","price":24.0000,"qty":0.0000,"amount":0.0000,"costPrice":16.0000,"saleLine":"09efe69a-7b61-4c67-a8c3-05c859e4210d"},{"uuid":"7a6a9613-e3b5-44d4-bbc1-b9ae2d6bdde8","line":4,"shopSku":"cf3ecdb1-6c1c-4536-ac25-14fd774f377e","shopSkuBarcode":"0000050","platformSku":"","skuName":"伟龙饼干（散）","skuMunit":"公斤","price":14.0000,"qty":0.0000,"amount":0.0000,"costPrice":9.6000,"saleLine":"bb56bfd0-39a5-45d8-9f01-5d2785ed17ac"},{"uuid":"195e4c03-b327-4df2-88ac-291070314a64","line":5,"shopSku":"b0880e17-6121-4d39-87c2-d55cd43c3313","shopSkuBarcode":"0000035","platformSku":"","skuName":"黑皮雪饼仙贝（散）","skuMunit":"公斤","price":22.0000,"qty":0.0000,"amount":0.0000,"costPrice":7.0000,"saleLine":"8fb2b7d5-4aff-4867-95dc-0bfb41c4b6e7"}],"payments":[{"uuid":"3da970b3-7cdc-4b3b-9256-b55c2c01a6ad","line":1,"paymentMethod":"CASH","paidAmount":14.1000,"payingAmount":0.0000,"payChannel":"CASH","created":"2017-11-11T08:59:47.000+0800"}],"sale":"a919eb97-5d2f-411b-9348-1f4a8902e807","saleNumber":"171111-041","shiftName":"早班","shiftNameDate":"2017-11-11T00:00:00.000+0800","_dcbustype":"salereturn"} {"uuid":"ba98867d-f05c-4b3e-9b2c-81cd1a46ee41","version":0,"createInfo":{"time":"2017-11-23T08:53:09.000+0800","operator":{"id":"18900000201","fullName":"201","qualifiedId":"18900000201"}},"lastModifyInfo":{"time":"2017-11-23T08:53:09.000+0800","operator":{"id":"18900000201","fullName":"201","qualifiedId":"18900000201"}},"number":"171123-002","encryptNumber":"171123-7449","shop":"a03a8283","posNo":"1","shiftName":"早班","shiftNameDate":"2017-11-23T00:00:00.000+0800","amount":2.0000,"discountAmount":0.0000,"changeAmount":0.0000,"qty":1.0000,"remark":"收银员手动点已付","lines":[{"uuid":"6ea72092-5db0-4973-a3b6-8f464c42758d","line":1,"shopSku":"9ebe9e92-13d0-4b7a-9493-9b841d3b4fe7","shopSkuBarcode":"6944591800691","platformSku":"4E878E6DFFBB49268CCB9B8CE46F5344","skuName":"创越漱口杯","skuMunit":"只","price":2.0000,"skuSalePrice":3.0000,"qty":1.0000,"amount":2.0000,"costPrice":2.2200,"saleLineFavItems":[{"uuid":"c6fbf7f4-ce5a-4a39-a623-aecf45a4bc02","price":0,"amount":0,"discount":1,"favType":"ChangePriceLineFavItem","favAmount":1.0000,"causeType":"MatchQfRuleFavCause","causeJson":"{\"tenant\":\"f62ba32a-b432-4786-9cf9-f7805d71dc40\",\"_cause_type_\":\"MatchQfRuleFavCause\",\"activity\":{\"id\":\"f01adece-30ea-4e62-a0c5-23f2d1c64b80\",\"namespace\":\"com.qianfan123.mp::Activity\"}}","activity":"f01adece-30ea-4e62-a0c5-23f2d1c64b80","activityNamespace":"com.qianfan123.mp::Activity","dependOnLines":[]}]}],"payments":[{"uuid":"b6cd982e-fdc5-416e-94d2-c54d61dbeeca","line":3,"paymentMethod":"ALIPAY","paidAmount":0.0100,"payingAmount":0.0100,"state":"PROCESSING","artificialState":"PAID","tranId":"0be234ea-6560-4146-8b09-3b96f1ff5ed9","payChannel":"ONLINE","created":"2017-11-23T08:53:09.000+0800"},{"uuid":"23d60861-a207-4f36-a0b4-f73381fb6f5f","line":4,"paymentMethod":"CASH","paidAmount":1.9900,"payingAmount":0.0000,"payChannel":"CASH","created":"2017-11-23T08:53:09.000+0800"}],"favDetails":[],"favItems":[],"_dcbustype":"sale"}

From: tao tony [mailto:tonytao0505@outlook.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