0

When I tried to copy a very large txt file into my postgres database, I got a following error below.

Note that I created a table with a single column and am not using any delimiter when importing the txt file.

db1=# create table travis_2018_data (v text); 
db1=# \COPY travis_2018_data FROM 'C:\Users\testu\Downloads\travis_2018\2018-Certification\PROP.txt';

The error:

ERROR: extra data after last expected column
CONTEXT: COPY travis_2018_data, line 295032: "000000561125P 02018000000000000

I'm wondering why I still get the error about the extra data (or column) on line 295032 ?

Jov
  • 83
  • 1
  • 2
  • 12
  • Welcome to Stack Overflow! Other users marked your question for low quality and need for improvement. I re-worded/formatted your input to make it easier to read/understand. Please review my changes to ensure they reflect your intentions. Feel free to drop me a comment in case you have further questions or feedback for me. – GhostCat Oct 25 '18 at 07:03

1 Answers1

0

Your text probably contains a tab character which is the default column delimiter for the TEXT format when using \copy (or copy) without specifying a format.

So \copy thinks the line contains two column but only expects one, hence the error message

You need to specify a delimiter that will not occur in the file. The character with the ASCII value 1 is a highly unlikely to occur in such a file, so you can try:

 \COPY travis_2018_data FROM '.....' DELIMITER E'\x01'