0

I'm inserting data into a PostgreSQL (15) database from a quite large CSV file containing image metadata but I'm facing the error at some point:

ERROR:  extra data after last expected column
CONTEXT:  COPY table, line 1009: "/path/to/Folder 1 2012/STA5584 60637/410712..."

This is not verbose enough for me. Is there something I can do in order to have more details so that I would be able to debug this properly?

For example, being able to see what comes after the three little dots, or also having a clue on the number of expected columns and the number it was actually trying to insert because there is probably a misaligned separator somewhere. Or even better, to see the full query.

1
  • What more details do you want?
    – jjanes
    Commented Mar 10, 2023 at 17:30

1 Answer 1

1

If you look in the server's log file, it will give you the text of the command which failed. Although that usually shouldn't be necessary; if you are running interactively, you should know what command you just issued. If you are running a script through psql, then psql itself would tell you what line number of the script contained the command which caused the problem.

In my hands it prints much more than you show before it truncates with the ellipsis (100 characters). This is not configurable except by changing the source code and recompiling. But once you know the command, you can look in record 1009 of the referenced input file to see what the full line was. This can be slightly tricky, as it is really counting records, not lines. If any fields contain embedded newlines (i.e. enclosed in quotes) the line number and record number will not match exactly. Generally I find that knowing the approximate line number and the first 100 characters of it is enough to identify the line pretty readily.

The number of columns it was looking for is discerned pretty readily once you have the command. It is either the number of columns specified in the command, or the if none were then the number of columns in the table. The actual number of columns found is one more than that, as it doesn't keep trying to parse out columns once it found too many of them.

It might be useful to know the exact character/byte number within the line at which it deduced there were too many columns, but I don't see a way to get that. That would be a feature request.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.