22P04ERRORTier 2 — Caution✅ HIGH confidence

bad copy file format

Category: Data ExceptionVersions: All Postgres versions

What this means

SQLSTATE 22P04 is raised when a file or data stream provided to the COPY command has a format that does not match the specified format options — for example, wrong number of columns, malformed CSV, or missing headers.

Why it happens

1COPY CSV file has a different number of columns than the target table or column list
2COPY text format uses unexpected delimiter or line endings
3Binary COPY file has an invalid header or signature
4CSV file has unmatched quote characters or embedded newlines not properly quoted

How to reproduce

COPY from a CSV file with mismatched column count.

trigger — this will ERROR

COPY employees (id, name, department) FROM '/tmp/data.csv' WITH (FORMAT CSV);
-- data.csv has only 2 columns but 3 are expected

ERROR: extra data after last expected column

Fix 1: Match the column list in the COPY command to the actual file structure

When the file has fewer or more columns than the table.

fix

-- If file has only id and name:
COPY employees (id, name) FROM '/tmp/data.csv' WITH (FORMAT CSV);

Why this works

Explicitly listing columns in the COPY command tells Postgres which table columns correspond to the file columns.

Fix 2: Inspect the file format options

When the format error is about delimiters or quoting.

fix

COPY employees FROM '/tmp/data.csv'
  WITH (FORMAT CSV, DELIMITER ',', QUOTE '"', HEADER true);

Why this works

Specifying DELIMITER, QUOTE, and HEADER explicitly ensures the COPY parser matches the actual file format.

Sources

📚 Official docs: https://www.postgresql.org/docs/current/errcodes-appendix.html

📚 Feature docs: https://www.postgresql.org/docs/current/sql-copy.html

🔧 Source ref: Class 22 — Data Exception (Postgres-specific)

Confidence assessment

✅ HIGH confidence

Postgres-specific error for COPY format validation. Stable across versions.