The bad_copy_file_format error (22P04) appears when COPY cannot parse the input file because its structure does not match the declared format options.
PostgreSQL bad_copy_file_format (22P04) occurs when COPY cannot parse the data file. Check delimiters, null markers, headers, and encoding, then rerun COPY with corrected format.
bad_copy_file_format
PostgreSQL raises error 22P04 – bad_copy_file_format – when the COPY command cannot parse the source data according to the supplied format options. The server detects unexpected field counts, unmatched quotes, or incompatible encodings and aborts the import.
The error stops data ingestion, leaving the target table unchanged.
Resolving it quickly matters because partial or failed loads block analytics, ETL pipelines, and downstream applications.
Incorrect delimiters, quote marks, or escape characters make COPY misinterpret column boundaries, producing bad_copy_file_format instantly.
Mismatched column counts between the file and table definition trigger the same error as soon as PostgreSQL meets a shorter or longer row.
Encoding differences such as UTF-8 data loaded with SQL_ASCII or mismatched null strings (e.g., "NULL" vs "\\N") also break parsing.
First, inspect the file with a text editor or csvkit to find malformed lines.
Count delimiters per row and confirm quoting rules.
Next, rerun COPY with explicit FORMAT, DELIMITER, NULL, QUOTE, and ENCODING parameters that match the file. Trim header rows or use HEADER TRUE if needed.
Large files benefit from testing a sample via COPY ... FROM PROGRAM 'head -n 100 file.csv' to validate format before a full load.
CSV exported from Excel often uses Windows-1252 encoding.
Add ENCODING 'WIN1252' or convert the file to UTF-8 with iconv to avoid 22P04.
Files created by pg_dump may include a header line. Use COPY ...
CSV HEADER to ignore it.
If a JSON column contains commas, wrap the JSON with double quotes and specify QUOTE '"' and ESCAPE '"' to keep COPY aligned.
Validate data files in CI pipelines with csvlint or similar linters before copying into production.
Always specify explicit format options in COPY instead of relying on defaults so future script changes remain safe.
Log rejected rows to a staging table using COPY ...
LOG ERRORS (PostgreSQL 14+) or load into a temporary table for review.
Error 22004 null_value_not_allowed appears when a NOT NULL column receives NULL during COPY. Define default values or allow NULL.
Error 22P05 untranslatable_character is thrown when encoding conversion fails. Set correct ENCODING or sanitize the file.
Error 23502 not_null_violation happens when a row lacks a required column. Align file columns to the table schema.
.
PostgreSQL 14+ supports COPY ... LOG ERRORS to record bad rows and continue loading.
Use COPY ... FROM PROGRAM 'tail -n +start file.csv' in a loop or load into a staging table with row numbers.
No. COPY is transactional. On error, the entire statement rolls back and existing table data stays intact.
Galaxy highlights COPY errors inline, lets you preview CSV files, and suggests correct COPY syntax with its AI copilot.
.avif)
.avif){kind=logo}
