How to fix Bad copy file format in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 26, 2025· 162 views

The PostgreSQL COPY command failed because the input file format does not match the expected format. This typically occurs due to delimiter mismatches, encoding issues, or structural problems in the data file.

Quick fix

Fixes Bad copy file format

COPY table_name FROM '/path/to/file.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',');
  1. Verify your COPY command syntax
  2. Inspect the input file for delimiter consistency
  3. Check line ending consistency
  4. Verify file encoding matches the database
postgresql — error
COPY table_name FROM '/path/to/file.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',');
Bad copy file format

What this error means

This error (error code 22P04) occurs when PostgreSQL cannot parse the format of a file being imported with the COPY command. PostgreSQL expects the file to conform to the format specified in your COPY statement—whether that is text, CSV, or binary format. When the file structure does not match these specifications, PostgreSQL rejects the entire operation.

Typical symptoms

  • COPY command fails with error code 22P04
  • Data import operations hang or terminate unexpectedly
  • Some rows import successfully before the error occurs
  • Error message references line numbers where format mismatches occur

Common causes

  1. Delimiter in the file does not match the DELIMITER specified in the COPY command
  2. File uses CSV format but command expects plain text or vice versa
  3. Inconsistent line endings (mixed newlines and carriage returns) in the input file
  4. File encoding (UTF-8, Latin-1, etc.) does not match database encoding
  5. CSV file has improper quoting or escaping of special characters
  6. Column count in data rows does not match the expected table columns
  7. NULL value representation in the file does not match the NULL specification in COPY

How to fix "Bad copy file format"

1Verify your COPY command syntax

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

sql
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.

2Inspect the input file for delimiter consistency

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:

bash
id,name,email
1,John Doe,john@example.com
2,Jane Smith,jane@example.com

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

3Check line ending consistency

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:

bash
dos2unix /path/to/file.csv

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

4Verify file encoding matches the database

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

sql
SHOW server_encoding;

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

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

Or convert the file to the correct encoding using iconv:

bash
iconv -f ISO-8859-1 -t UTF-8 input.csv -o output.csv
5Handle CSV quoting and escaping correctly

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:

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

You can customize quote and escape characters:

sql
COPY table_name FROM '/path/to/file.csv'
WITH (FORMAT csv, QUOTE '"', ESCAPE '\\');
6Verify column count and order

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:

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

If importing into specific columns, list them:

sql
COPY table_name (id, name, email) FROM '/path/to/file.csv' WITH (FORMAT csv, HEADER true);
7Test with a small subset of data

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

bash
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.

Advanced notes

PostgreSQL 18+ introduced enhanced error handling for COPY with the ON_ERROR option, allowing you to skip invalid rows instead of failing the entire command:

sql
COPY table_name FROM '/path/to/file.csv'
WITH (FORMAT csv, ON_ERROR ignore);

For binary format COPY, the binary protocol is version-specific and platform-dependent. Do not mix binary data from different PostgreSQL versions or architectures without careful validation.

If working with data from Windows systems, be aware that different tools may export CSV with different defaults for delimiters and quote characters. Tools like csvkit can help validate and clean CSV data before importing:

bash
csv-validate /path/to/file.csv

For very large files, consider using COPY TO with a plain text format first, then inspect a sample to understand the actual structure before attempting COPY FROM.

Frequently asked questions

What does "Bad copy file format" mean?
This error (error code 22P04) occurs when PostgreSQL cannot parse the format of a file being imported with the COPY command. PostgreSQL expects the file to conform to the format specified in your COPY statement—whether that is text, CSV, or binary format. When the file structure does not match these specifications, PostgreSQL rejects the entire operation.
How do I fix "Bad copy file format"?
The PostgreSQL COPY command failed because the input file format does not match the expected format. This typically occurs due to delimiter mismatches, encoding issues, or structural problems in the data file.
What causes "Bad copy file format"?
Delimiter in the file does not match the DELIMITER specified in the COPY command File uses CSV format but command expects plain text or vice versa Inconsistent line endings (mixed newlines and carriage returns) in the input file
Is the "Bad copy file format" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading