How to Fix 'invalid byte sequence for encoding "UTF8": 0xc92c' Error When Importing CSV Data into PostgreSQL

Table of Contents#

1. Understanding the Error
2. Common Causes of the Error
3. How to Diagnose the Issue
   • Step 1: Determine the CSV File’s Encoding
   • Step 2: Locate the Problematic Byte Sequence
4. Step-by-Step Fixes
   • Fix 1: Convert the CSV File to UTF-8
   • Fix 2: Specify the Correct Encoding in PostgreSQL
   • Fix 3: Clean Corrupted Bytes or NUL Characters
5. Preventive Measures to Avoid Future Errors
6. Conclusion
7. References

Understanding the Error#

Let’s dissect the PostgreSQL error message:

invalid byte sequence for encoding "UTF8": 0xc92c

• invalid byte sequence for encoding "UTF8": PostgreSQL expects all imported text bytes to conform to the UTF-8 specification.
• 0xc92c: This is the hex representation of the bytes that triggered the failure:
  • In UTF-8, characters are variable-length (1 to 4 bytes). Single-byte ASCII characters range from 0x00 to 0x7F.
  • Leading bytes in the range 0xC2 to 0xDF indicate the start of a 2-byte character and must be followed by a continuation byte in the range 0x80 to 0xBF (binary pattern 10xxxxxx).
  • Here, 0xC9 (binary 11001001) signals a 2-byte UTF-8 sequence. However, the subsequent byte is 0x2C (binary 00101100, which represents an ASCII comma ,). Because 0x2C is not a valid UTF-8 continuation byte, the parser fails.

In single-byte encodings like ISO-8859-1 (Latin-1) or Windows-1252 (WIN1252), 0xC9 represents the uppercase letter É. If a CSV row contains ...É,..., the raw bytes are 0xC9 0x2C. When PostgreSQL reads this under UTF-8 mode, it interprets 0xC9 as a multi-byte header rather than a standalone É, resulting in the 0xc92c error.

Common Causes of the Error#

The root cause is almost always an encoding mismatch or invalid character data in the source file:

1. The CSV File Uses a Single-Byte / Non-UTF-8 Encoding#

CSV exports from legacy databases, enterprise software, or default desktop applications often use regional encodings, such as:

• ISO-8859-1 (Latin-1): Common for Western European languages (É, ñ, ü).
• Windows-1252 (CP1252 / WIN1252): Extends Latin-1 with characters like curly quotes (“ ”) and the euro sign (€).
• ISO-8859-15: Similar to Latin-1 with updated currency and language symbols.

2. Embedded NUL (0x00) or Corrupted Control Bytes#

PostgreSQL text data types (TEXT, VARCHAR, CHAR) do not allow NUL (0x00) bytes because PostgreSQL's internal C engine treats 0x00 as a string terminator. If your data contains raw binary bytes or corrupted data transfers, the import will fail.

3. Application Export Defaults#

Spreadsheet software (like Microsoft Excel) historically exported CSV files using the operating system's local ANSI codepage (e.g., Windows-1252 on Western Windows systems) unless "CSV UTF-8" was explicitly selected.

How to Diagnose the Issue#

Before applying a fix, identify the actual encoding and locate where the offending bytes reside in the file.

Step 1: Determine the CSV File’s Encoding#

Use one of the following methods to detect the file's character set:

Option A: file Command (Linux / macOS)#

Run file with the MIME encoding flag:

# Linux
file -i your_file.csv
 
# macOS
file -I your_file.csv

Example output:

your_file.csv: text/plain; charset=iso-8859-1

Option B: Python chardet or charset_normalizer#

Python tools can analyze the byte patterns with high confidence:

pip install chardet
chardetect your_file.csv

Example output:

your_file.csv: ISO-8859-1 with confidence 0.85

Option C: GUI Text Editors (VS Code / Notepad++)#

• VS Code: Check the bottom status bar on the right. Click the encoding name (e.g., "UTF-8" or "Windows 1252") and choose Reopen with Encoding to test if characters like accented letters display correctly.
• Notepad++: The detected character set is displayed in the bottom-right corner and under the Encoding menu.

Step 2: Locate the Problematic Byte Sequence#

To see the exact line and context of the byte sequence 0xc92c, inspect the file using command-line tools:

Using hexdump / xxd:#

hexdump -C your_file.csv | grep -C 2 "c9 2c"

or with xxd:

xxd your_file.csv | grep -C 2 "c92c"

Using grep to Find Non-ASCII Lines:#

grep -n -axv '.*' your_file.csv

This prints the line numbers containing non-ASCII or invalid characters.

Step-by-Step Fixes#

Choose the solution that best fits your workflow: converting the file beforehand, configuring PostgreSQL to convert it on the fly, or sanitizing damaged bytes.

Fix 1: Convert the CSV File to UTF-8#

Converting the file to UTF-8 before importing is the cleanest and most portable approach.

Method 1: Using iconv (Linux / macOS)#

Use iconv to convert from the source encoding to UTF-8:

# Syntax: iconv -f <SOURCE_ENCODING> -t UTF-8 <INPUT_FILE> > <OUTPUT_FILE>
iconv -f ISO-8859-1 -t UTF-8 your_file.csv > your_file_utf8.csv

If the file is Windows-1252:

iconv -f WINDOWS-1252 -t UTF-8 your_file.csv > your_file_utf8.csv

Tip: Add the -c flag to skip unconvertible or corrupt bytes:

iconv -c -f ISO-8859-1 -t UTF-8 your_file.csv > your_file_utf8.csv

Method 2: Using Python#

Python handles character conversions cleanly and allows you to specify fallback error strategies:

input_path = "your_file.csv"
output_path = "your_file_utf8.csv"
source_encoding = "iso-8859-1"  # or "cp1252"
 
with open(input_path, "r", encoding=source_encoding, errors="replace") as src:
    with open(output_path, "w", encoding="utf-8") as dst:
        for line in src:
            dst.write(line)

Or using Pandas:

import pandas as pd
 
df = pd.read_csv("your_file.csv", encoding="iso-8859-1")
df.to_csv("your_file_utf8.csv", index=False, encoding="utf-8")

Method 3: Using Spreadsheet Software (Excel / LibreOffice)#

• Microsoft Excel: Open the file, click File > Save As, and choose CSV UTF-8 (Comma delimited) (*.csv) from the file type dropdown.
• LibreOffice Calc: Open the CSV, select the detected character set in the Text Import dialog, then choose File > Save As > Text CSV, check Edit filter settings, and select Character set: Unicode (UTF-8).

Fix 2: Specify the Correct Encoding in PostgreSQL#

If you prefer to import the file directly without pre-converting it, instruct PostgreSQL to handle character conversion automatically during import.

Using the ENCODING Option in COPY#

PostgreSQL's server-side COPY command accepts an ENCODING parameter:

COPY your_table (column1, column2)
FROM '/absolute/path/to/your_file.csv'
WITH (
    FORMAT CSV,
    HEADER true,
    DELIMITER ',',
    ENCODING 'LATIN1' -- Or 'WIN1252', 'ISO_8859_1'
);

Using \copy in psql (Client-Side)#

When running imports from your local machine with psql, use the \copy meta-command with the ENCODING clause:

\copy your_table (column1, column2) FROM 'your_file.csv' WITH (FORMAT CSV, HEADER true, DELIMITER ',', ENCODING 'LATIN1');

Setting client_encoding for the Session#

Alternatively, set the client encoding before running your import. PostgreSQL will automatically translate strings from the client encoding into the database server's UTF-8 encoding:

-- Set client encoding to match the file's encoding
SET client_encoding = 'LATIN1';
 
-- Execute the copy command
COPY your_table FROM '/absolute/path/to/your_file.csv' WITH (FORMAT CSV, HEADER true);
 
-- Reset back to UTF8 when finished
SET client_encoding = 'UTF8';

Note on PostgreSQL Encoding Names: Canonical PostgreSQL names for Western encodings include 'LATIN1', 'WIN1252', 'ISO_8859_1', and 'UTF8'. Check your database encoding anytime with SHOW server_encoding; and your current session encoding with SHOW client_encoding;.

Fix 3: Clean Corrupted Bytes or NUL Characters#

If the error is caused by invalid binary bytes (e.g., stray characters or NUL 0x00), you must strip or sanitize them before PostgreSQL can accept the rows.

Removing Specific Byte Sequences with sed#

If 0xc9 0x2c is corrupted binary noise rather than valid text, remove it with sed:

sed -i 's/\xc9\x2c//g' your_file.csv

Removing NUL (0x00) Bytes#

If your import fails with invalid byte sequence for encoding "UTF8": 0x00, strip all NUL bytes using tr:

tr -d '\000' < your_file.csv > cleaned_file.csv

Handling Bad Rows in PostgreSQL 17+#

Starting in PostgreSQL 17, the COPY command supports the ON_ERROR ignore parameter, allowing the import to skip malformed rows without aborting the entire transaction:

COPY your_table
FROM '/absolute/path/to/your_file.csv'
WITH (
    FORMAT CSV,
    HEADER true,
    ON_ERROR ignore
);

Preventive Measures to Avoid Future Errors#

To prevent encoding mismatches in ongoing data pipelines:

1. Standardize on UTF-8 at the Source: Configure database exports, data extract scripts, and ETL pipelines to output UTF-8 explicitly (e.g., df.to_csv('output.csv', encoding='utf-8', index=False)).
2. Explicitly Save as CSV UTF-8 in Excel: Always choose CSV UTF-8 (Comma delimited) when exporting tabular data from Microsoft Excel.
3. Automate Encoding Validation: Add a pre-import validation step in your ingest pipeline using csvkit or Python:

   csvclean your_file.csv

4. Specify ENCODING in Automated COPY Scripts: If data sources are known to produce non-UTF-8 files, always include the explicit ENCODING 'LATIN1' or ENCODING 'WIN1252' option in your automated SQL ingestion scripts.

Conclusion#

The invalid byte sequence for encoding "UTF8": 0xc92c error is caused by an encoding mismatch between a non-UTF-8 CSV file (such as Latin-1 or Windows-1252) and PostgreSQL's UTF-8 character requirements.

By diagnosing the source encoding with tools like file or chardet, converting the file using iconv or Python, or specifying the ENCODING parameter directly in PostgreSQL's COPY / \copy command, you can resolve the error and ensure reliable data imports.

References#

• PostgreSQL Documentation: Character Set Support
• PostgreSQL Documentation: COPY Command
• iconv(1) Linux Manual Page
• chardet: Character Encoding Auto-Detection for Python
• W3C: Model for Tabular Data and Metadata on the Web