From: | tao tony <tonytao0505(at)outlook(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | copy error with json/jsonb types |
Date: | 2017-11-30 06:01:55 |
Message-ID: | MWHPR13MB138974273F25E3F50980739BAA380@MWHPR13MB1389.namprd13.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 | Date | Subject | |
---|---|---|---|
Next Message | Durumdara | 2017-11-30 07:35:20 | Searching for big differences between values |
Previous Message | Emanuel Alvarez | 2017-11-30 05:23:23 | Re: pg_replication_slots |