How to fix Bad copy file format in PostgreSQL

Check that your COPY command correctly specifies the file format. Common formats are TEXT (default), CSV, and BINARY. For example:

COPY table_name FROM '/path/to/file.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',');

Ensure the FORMAT clause matches how your file is actually structured.

Open the file in a text editor and verify that all rows use the same delimiter character throughout. If using CSV with comma delimiters, ensure commas are used consistently. For example, if your COPY command specifies DELIMITER ',', every data row should use commas:

id,name,email
1,John Doe,[email protected]
2,Jane Smith,[email protected]

If some rows use different delimiters (tabs, pipes, etc.), they will cause format errors.

PostgreSQL requires all line endings to be consistent throughout the file. The file should use either all LF (Unix), all CRLF (Windows), or all CR (old Mac), but not a mix. Use a tool like dos2unix or your editor to normalize line endings:

dos2unix /path/to/file.csv

Or in your editor, set "line endings" to either LF or CRLF consistently.

Check your database encoding and ensure the input file uses the same encoding. View your database encoding with:

SHOW server_encoding;

If your database uses UTF-8 but your file is Latin-1, specify the encoding in the COPY command:

COPY table_name FROM '/path/to/file.csv' WITH (FORMAT csv, ENCODING 'UTF-8');

Or convert the file to the correct encoding using iconv:

iconv -f ISO-8859-1 -t UTF-8 input.csv -o output.csv

In CSV format, fields containing special characters (commas, quotes, newlines) must be quoted. Verify that quoted fields use the correct escape mechanism. By default, a quote inside a quoted field is escaped by doubling it:

id,name,notes
1,"John ""Johnny"" Doe","Contains, comma"

You can customize quote and escape characters:

COPY table_name FROM '/path/to/file.csv'
WITH (FORMAT csv, QUOTE '"', ESCAPE '\\');

Ensure every row in your file has the same number of columns as your table (or the column list you specified). If your file has headers, use the HEADER option:

COPY table_name FROM '/path/to/file.csv' WITH (FORMAT csv, HEADER true);

If importing into specific columns, list them:

COPY table_name (id, name, email) FROM '/path/to/file.csv' WITH (FORMAT csv, HEADER true);

If the file is large, extract the first 10 rows and test the COPY command:

head -n 10 /path/to/large_file.csv > /tmp/test_file.csv

Then run the COPY command on the smaller test file. This helps isolate whether specific rows are causing the format error.