31

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
nathanmgroom
  • 417
  • 1
  • 5
  • 6
  • 1
    What does the data look like in the file? (A few rows worth of sample.) – khampson Nov 05 '14 at 00:54
  • The error could also be due to the order of the columns in the CSV not matching the order of how the columns are defined in postgres. You can get around this by specifying the column names in your `\COPY` command. Something like `COPY table_name (col1, col2) FROM '/path/to/file.csv'`. And no, [using `HEADERS` doesn't actually read and sort the headers](https://www.postgresql.org/docs/current/sql-copy.html) like you might expect, it just tells postgres to ignore the first row of the file. – Hartley Brody Jan 24 '23 at 14:57

4 Answers4

14

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?

  1. 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.
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 2
    I don't think it's the first one because I changed my delimiter to ',' and the same thing happened. I don't think it's the second one because I don't have a single backslash in my entire file (I did a search with ctrl-F). If it's the 3rd, I'm not sure how to fix it. I try the COPY command in the pgadmin GUI and I get this error: ERROR: could not open file "/users/nathangroom/desktop/DOE_inventory5.txt" for reading: Permission denied. I saw elsewhere on StackOverflow that using the \copy command in the command shell was the solution for that error. – nathanmgroom Nov 05 '14 at 01:52
  • @nathanmgroom: Where is your file? Where is the Postgres server? Where is your client? Also, you can't just change the delimiter arbitrarily. It must be the one used in your file. Which is it? The default is a tab character in text format. – Erwin Brandstetter Nov 05 '14 at 02:01
  • The file's on my desktop, I don't know exactly where the postgres server and the client are but I do know that it's all local, on my machine. The server says it's localhost 5432. I tried it again after saving it as a csv and I get this error: ERROR: invalid input syntax for integer: "Laboratory_ID" Which makes no sense because there's definitely an integer in that column. – nathanmgroom Nov 05 '14 at 02:21
  • @nathanmgroom: The last error indicates you included a header line with the names of each column, which is allowed in csv mode using the `HEADER` option, but not in (default) text mode. Remove the line and try again. Also, if everything is running locally, you can use `\copy` as well as `COPY` - both access the same file system. But `COPY` is more restrictive with permissions. – Erwin Brandstetter Nov 05 '14 at 02:25
  • Hi, I tried that and it eliminated that error, but now I'm getting a new error, the one I got originally: \copy DOE3 from '/users/nathangroom/desktop/DOE_inventory2.csv' (DELIMITER(',')) ERROR: missing data for column "ZIP Code" CONTEXT: COPY doe3, line 1: "DOE,Ames Laboratory,1,Materials Preparation Center,"Technical and Administrative Services Facility" – nathanmgroom Nov 05 '14 at 03:02
  • 1
    This answer works for me. I had the same error and found out that the hint was *point 1*. One of the data fields in my `.csv` contained a comma due to erroneous conversion by the tool I had used. Reconverting the data to csv with a better tool solved the issue. Thank you! – Lym Sep 21 '15 at 17:34
  • I edited it using and it deleted spaces at end of line! :-O – Manel Clos Mar 26 '20 at 10:34
4

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
Nagev
  • 10,835
  • 4
  • 58
  • 69
2

I had a similar error. check the version of pg_dump that was used in exporting the data and the version of the database you are want to insert it into. make sure they are same. Also, if copy export fails then export the data by insert

Aniefiok
  • 31
  • 1
  • 5
0

Try to delete table and create once again with column default value if possible, as my case i had same error and that resolve with deleting table and recreating with adding default value to column!

PriteshSurale
  • 23
  • 5