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"}
