CODEWITHSUNDEEP
postgresqlpsqlpostgresql-copy
nathanmgroom
nathanmgroom

Reputation: 437

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

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?

Upvotes: 33

Views: 57852

Answers (6)

Enrico Ferreguti
Enrico Ferreguti

Reputation: 498

Check the column delimiter. If it's wrong, a row is imported as one column and the interpreter fails to recognize further exprected columns

Upvotes: 1

Constantinos Petrakis
Constantinos Petrakis

Reputation: 91

In my case (I don't care about whitespaces) replacing all whitespaces with single spaces fixed the problem. The following python script utilizing pandas could get the job done:

import pandas as pd

df = pd.read_csv("error.csv")
df.replace({r"\s+": " "}, inplace=True, regex=True)
df.to_csv("no_error.csv", index=False)

As others have mentioned the error was probably caused by non property escaped characters messing with the csv format.

Upvotes: 1

PriteshSurale
PriteshSurale

Reputation: 23

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!

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656291

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.

Upvotes: 16

Aniefiok
Aniefiok

Reputation: 61

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

Upvotes: 3

Nagev
Nagev

Reputation: 13207

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

Upvotes: 7

Related Questions