EC
22P04
PostgreSQLERRORNotableData ExceptionHIGH confidence

bad copy file format

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
  1. 1COPY CSV file has a different number of columns than the target table or column list
  2. 2COPY text format uses unexpected delimiter or line endings
  3. 3Binary COPY file has an invalid header or signature
  4. 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
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

expected output

ERROR:  extra data after last expected column

Fix 1

Match the column list in the COPY command to the actual file structure

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

Match the column list in the COPY command to the actual file structure
-- 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 When the format error is about delimiters or quoting.

Inspect the file format options
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 documentation ↗

Class 22 — Data Exception (Postgres-specific)

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All PostgreSQL errors