ERROR: missing data for column when using \copy in psql

Question

I'm trying to import a .txt file into PostgreSQL. The txt file has 6 columns:

Laboratory_Name Laboratory_ID   Facility    ZIP_Code     City   State

And 213 rows.

I'm trying to use \copy to put the contents of this file into a table called doe2 in PostgreSQL using this command:

\copy DOE2 FROM '/users/nathangroom/desktop/DOE_inventory5.txt' (DELIMITER(' '))

It gives me this error:

missing data for column "facility"

I've looked all around for what to do when encountering this error and nothing has helped. Has anyone else encountered this?

Answer 1

Three possible causes:

1. One or more lines of your file has only 4 or fewer space characters (your delimiter).

2. One or more space characters have been escaped (inadvertently). Maybe with a backslash at the end of an unquoted value. For the (default) text format you are using, the manual explains:

   Backslash characters (\) can be used in the COPY data to quote data characters that might otherwise be taken as row or column delimiters.

Output from COPY TO or pg_dump would not exhibit any of these faults when reading from a table with matching layout. But maybe your file has been edited or is from a different, faulty source?

3. You are not using the file you think you are using. The \copy meta-command of the psql command-line interface is a wrapper for COPY and reads files local to the client. If your file lives on the server, use the SQL command COPY instead.

Answer 2

Check the file carefully. In my case, a blank line at the end of the file caused the ERROR: missing data for column. Deleted it, and worked fine.

Printing the blank lines might reveal something interesting:

cat -e $filename