Error importing JSON file into Postgres 16 Database (22P04 extra data after last expected column)

Question

So I have the following file '14.json' which i am trying to import into a staging table on a postgres 16 database

Now I couldnt attach the file..but its contents are here:

14.json

{ "ChannelReadings": [
        { "ReadingsDto": [
                {
                    "Si": 47.67,
                    "Raw": 0,
                    "Conversion": 0,
                    "TimeStamp": "2023-01-24T12:57:43"
                },
                {
                    "Si": 47.22,
                    "Raw": 0,
                    "Conversion": 0,
                    "TimeStamp": "2023-01-24T13:02:43"
                },
                {
                    "Si": 47.6,
                    "Raw": 0,
                    "Conversion": 0,
                    "TimeStamp": "2023-01-24T13:07:43"
                },
                {
                    "Si": 47.5,
                    "Raw": 0,
                    "Conversion": 0,
                    "TimeStamp": "2023-01-24T13:12:43"
                }
            ],
            "ChannelId": 14
        },
        {
            "ReadingsDto": [
                {
                    "Si": 2.893605,
                    "Raw": 0,
                    "Conversion": 0,
                    "TimeStamp": "2023-01-24T13:07:43"
                }
            ],
            "ChannelId": 12
        },
        {
            "ReadingsDto": [
                {
                    "Si": 3.294233,
                    "Raw": 0,
                    "Conversion": 0,
                    "TimeStamp": "2023-01-24T13:07:43"
                }
            ],
            "ChannelId": 13
        },
        {
            "ReadingsDto": [
                {
                    "Si": 3.294233,
                    "Raw": 0,
                    "Conversion": 0,
                    "TimeStamp": "2023-01-24T13:07:43"
                }
            ],
            "ChannelId": 16
        }
    ],
    "DeviceSerialNumber": "894339",
    "RestartPointerNo": 5514732,
    "NewDownloadTable": false,
    "DataHashDto": "5Mckxoq42EeLHmLnimXv6A=="
}

my code to import this is as follows:

DROP TABLE IF EXISTS tmp;

CREATE TEMP  table tmp (
      c TEXT
    );


COPY tmp FROM 'C:\ChrisDev\Readings\14.json';

However I am getting the following error:

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

Ive tried editing this to no avail What am I doing wrong here?

Answer 1

You probably have tabs in your file, which is unfortunate for copy...format text (default behaviour) because another default setting copy comes with is delimiter configured to use tabs:

FORMAT Selects the data format to be read or written: text, csv (Comma Separated Values), or binary. The default is text.

DELIMITER Specifies the character that separates columns within each row (line) of the file. The default is a tab character in text format, a comma in CSV format. This must be a single one-byte character. This option is not allowed when using binary format.

To solve this, try another delimiter:

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