copy error with json/jsonb types

Поиск
Список
Период
Сортировка
От tao tony
Тема copy error with json/jsonb types
Дата
Msg-id MWHPR13MB138974273F25E3F50980739BAA380@MWHPR13MB1389.namprd13.prod.outlook.com
обсуждение исходный текст
Ответы RE: copy error with json/jsonb types
Список 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"}

В списке pgsql-general по дате отправления:

Предыдущее
От: Emanuel Alvarez
Дата:
Сообщение: Re: pg_replication_slots
Следующее
От: Durumdara
Дата:
Сообщение: Searching for big differences between values