22P04 error code in PostgreSQL corresponds to
bad_copy_file_format. This error typically occurs when using the
COPY command to load data from a file into a table, and the file format does not match the expected format by the
COPY command. Common issues include incorrect column count, mismatched data types, or improper handling of delimiters and quote characters.
To diagnose and fix this error, follow these steps:
- Check File Format: Ensure that the file you are trying to import matches the format expected by PostgreSQL. Common file formats for
COPYare CSV and text.
- Match Column Count: Verify that the number of columns in the file matches the number of columns in the target table.
- Data Type Consistency: Make sure that the data in each column of the file can be correctly cast to the corresponding column data type in the table.
- Delimiter and Quote Character: Confirm that the delimiter (e.g., a comma for CSV files) and quote character (often a double-quote for CSV) are used consistently and correctly throughout the file.
Here are some examples and sample code to illustrate how to fix the
Example 1: Incorrect Column Count
If your table has three columns but your CSV file has only two, you’ll get a
To fix the error, ensure the file has the correct number of columns:
COPY your_table FROM '/path/to/your/file.csv' WITH (FORMAT csv, HEADER);
Make sure the CSV file has the same number of columns as
Example 2: Mismatched Data Type
If a column in your CSV file contains text, but the corresponding column in the table is an integer, the
COPY command will fail.
To fix this, you can either modify the data in the file to match the data types of the table or alter the table to accommodate the data types in the file.
Example 3: Delimiter and Quote Character Issues
If your CSV file uses a semicolon as a delimiter instead of a comma, you need to specify this in the
COPY your_table FROM '/path/to/your/file.csv' WITH (FORMAT csv, DELIMITER ';', HEADER);
If your data contains the delimiter character within a field, that field should be enclosed in quotes. If the data also contains quote characters, they should be escaped by doubling them.
For example, a CSV line might look like this:
"Smith, John","123 Main St.; Apt 4","He said, ""Hello!"""
COPY command should handle this correctly as long as the file format is consistent.
For more information on the
COPY command and handling file formats, you can refer to the official PostgreSQL documentation and resources that discuss common
COPY command issues, such as Stack Overflow discussions and PostgreSQL error code explanations.