PostgreSQL 22P04 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 22P04: bad copy file format

PostgreSQL error 22P04 (bad_copy_file_format) occurs when the COPY command encounters a file whose content does not match the specified format options. This error is common in data migration, ETL pipelines, and bulk data loading scenarios where file format assumptions are incorrect or files become corrupted during transfer.

Top 3 Causes and Fixes

1. Mismatch Between File Format and COPY Options

The most frequent cause is specifying the wrong FORMAT option for the actual file. A CSV file loaded with FORMAT TEXT, or a tab-delimited file loaded with FORMAT CSV, will immediately trigger this error.

-- Wrong: trying to load a CSV file as plain TEXT
COPY employees FROM '/data/employees.csv' WITH (FORMAT TEXT);

-- Correct: match the FORMAT to the actual file
COPY employees FROM '/data/employees.csv' WITH (
    FORMAT CSV,
    HEADER true,
    DELIMITER ',',
    NULL '',
    ENCODING 'UTF8'
);

-- For tab-delimited files
COPY employees FROM '/data/employees.tsv' WITH (
    FORMAT TEXT,
    DELIMITER E'\t',
    NULL '\N'
);

-- Tip: inspect the raw file content using a temp table first
CREATE TEMP TABLE raw_check (line TEXT);
COPY raw_check FROM '/data/employees.csv' WITH (FORMAT TEXT);
SELECT * FROM raw_check LIMIT 5;

2. Binary Format Corruption or Version Mismatch

PostgreSQL binary COPY files (FORMAT BINARY) contain a strict file signature (PGCOPY\n\377\r\n\0) and header flags. If the file is corrupted during transfer (e.g., sent via FTP in text mode) or was generated by a different PostgreSQL major version or a third-party tool, the signature check fails and error 22P04 is raised.

-- Avoid binary format for cross-environment transfers; use CSV instead
-- Export safely with CSV
COPY employees TO '/data/employees_safe.csv' WITH (
    FORMAT CSV,
    HEADER true,
    ENCODING 'UTF8'
);

-- Import safely with CSV
COPY employees FROM '/data/employees_safe.csv' WITH (
    FORMAT CSV,
    HEADER true,
    ENCODING 'UTF8'
);

-- If you must use binary, verify the file signature (requires superuser)
SELECT encode(pg_read_binary_file('/tmp/employees.bin', 0, 11), 'escape') AS pg_copy_signature;
-- Expected output: PGCOPY\n\377\r\n\000

3. Line Ending Differences (CRLF vs LF)

Files created on Windows use \r\n (CRLF) line endings, while Linux/Unix uses \n (LF). When a Windows-generated file is loaded into a Linux-hosted PostgreSQL server in text mode, the \r character is treated as part of the data, causing format parsing to fail. This is especially common with CSV exports from Microsoft Excel.

-- Load into a staging table first, then clean up \r characters
CREATE TEMP TABLE employees_staging (LIKE employees);

COPY employees_staging FROM '/data/employees_windows.csv' WITH (
    FORMAT CSV,
    HEADER true
);

-- Strip carriage return characters from all text columns
UPDATE employees_staging
SET employee_name = REPLACE(employee_name, E'\r', '')
WHERE employee_name LIKE E'%\r%';

-- Move clean data to the target table
INSERT INTO employees SELECT * FROM employees_staging;
DROP TABLE employees_staging;

Quick Fix Checklist

Symptom	Fix
CSV file with wrong FORMAT	Use `FORMAT CSV` with correct `DELIMITER` and `HEADER`
Binary file error	Switch to `FORMAT CSV` for portability
`\r` in text fields	Run `dos2unix` on file or strip `\r` after staging load
Encoding error alongside 22P04	Add `ENCODING 'UTF8'` to COPY options

Prevention Tips

1. Always validate with a staging table before full load.

Never run COPY directly into a production table without testing with a small sample first. Use a temp table to catch format errors early.

-- Pre-flight check with temp table
CREATE TEMP TABLE preflight (LIKE target_table);
COPY preflight FROM '/data/input.csv' WITH (FORMAT CSV, HEADER true, ENCODING 'UTF8');
SELECT count(*) FROM preflight;
-- If no error, proceed with full load

2. Standardize your COPY format across the team.

Define a team-wide standard for all COPY operations — for example, always use FORMAT CSV, HEADER true, ENCODING 'UTF8', DELIMITER ',' — and enforce it through pipeline configuration files or wrapper scripts. This eliminates ambiguity when files are passed between different environments or team members.

-- Example of a consistent, documented COPY template
COPY table_name FROM '/path/to/file.csv' WITH (
    FORMAT CSV,       -- always explicit
    HEADER true,      -- always include header
    DELIMITER ',',    -- always comma
    NULL '',          -- define null representation
    ENCODING 'UTF8'   -- always UTF-8
);

Related Errors

22P05 (untranslatable_character): encoding conversion failure during COPY, often occurs alongside 22P04.
22007 (invalid_datetime_format): correct file format but wrong date/time field values.
58030 (io_error): file access or permission issues preventing COPY from reading the file at all.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community