ERROR: extra data after last expected column on postgres

Question

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 ?

Answer 1

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'