postgresql 将JSON文件导入Postgres 16数据库时出错(22P04在最后一个预期列之后有额外数据)

apeeds0o  于 2023-11-18  发布在  PostgreSQL
关注(0)|答案(1)|浏览(163)

所以我有下面的文件'14.json',我试图导入到postgres 16数据库的staging表中
现在我不能附加文件..但它的内容在这里:

14.json

  1. { "ChannelReadings": [
  2. { "ReadingsDto": [
  3. {
  4. "Si": 47.67,
  5. "Raw": 0,
  6. "Conversion": 0,
  7. "TimeStamp": "2023-01-24T12:57:43"
  8. },
  9. {
  10. "Si": 47.22,
  11. "Raw": 0,
  12. "Conversion": 0,
  13. "TimeStamp": "2023-01-24T13:02:43"
  14. },
  15. {
  16. "Si": 47.6,
  17. "Raw": 0,
  18. "Conversion": 0,
  19. "TimeStamp": "2023-01-24T13:07:43"
  20. },
  21. {
  22. "Si": 47.5,
  23. "Raw": 0,
  24. "Conversion": 0,
  25. "TimeStamp": "2023-01-24T13:12:43"
  26. }
  27. ],
  28. "ChannelId": 14
  29. },
  30. {
  31. "ReadingsDto": [
  32. {
  33. "Si": 2.893605,
  34. "Raw": 0,
  35. "Conversion": 0,
  36. "TimeStamp": "2023-01-24T13:07:43"
  37. }
  38. ],
  39. "ChannelId": 12
  40. },
  41. {
  42. "ReadingsDto": [
  43. {
  44. "Si": 3.294233,
  45. "Raw": 0,
  46. "Conversion": 0,
  47. "TimeStamp": "2023-01-24T13:07:43"
  48. }
  49. ],
  50. "ChannelId": 13
  51. },
  52. {
  53. "ReadingsDto": [
  54. {
  55. "Si": 3.294233,
  56. "Raw": 0,
  57. "Conversion": 0,
  58. "TimeStamp": "2023-01-24T13:07:43"
  59. }
  60. ],
  61. "ChannelId": 16
  62. }
  63. ],
  64. "DeviceSerialNumber": "894339",
  65. "RestartPointerNo": 5514732,
  66. "NewDownloadTable": false,
  67. "DataHashDto": "5Mckxoq42EeLHmLnimXv6A=="
  68. }

字符串
我的代码如下:

  1. DROP TABLE IF EXISTS tmp;
  2. CREATE TEMP table tmp (
  3. c TEXT
  4. );
  5. COPY tmp FROM 'C:\ChrisDev\Readings\14.json';


但我得到以下错误:

  1. [2023-11-16 12:44:32] [22P04] ERROR: extra data after last expected column
  2. [2023-11-16 12:44:32] Where: COPY tmp, line 2: " { "ReadingsDto": ["


我试着编辑这个没有用我在这里做错了什么?

64jmpszr

64jmpszr1#

您的文件中可能有制表符,这对copy...format text(默认行为)来说是不幸的,因为copy附带的另一个默认设置是delimiter配置为使用制表符:
FORMAT表示要读取或写入的数据格式:textcsv(逗号分隔值)或binary。默认值为text
DELIMITER指定文件每行(行)中分隔列的字符。默认值为text中的 * 制表符 *,CSV中的逗号。此字符必须是单个单字节字符。使用binary时不允许使用此选项。
要解决这个问题,请尝试另一种方法:

  1. DROP TABLE IF EXISTS tmp;
  2. CREATE TEMP table tmp (c TEXT );
  3. COPY tmp FROM 'C:\ChrisDev\Readings\14.json' WITH (FORMAT TEXT, DELIMITER '~');

字符串

相关问题