使用Npgsql 4.0.4插入含特殊字符数据触发UTF8编码异常求助

你碰到的这个问题其实是PostgreSQL的特性导致的——它的UTF8编码完全不允许存储空字符（0x00），不管是character varying还是text类型都遵循这个规则，因为PostgreSQL会把0x00当作字符串的结束标记，这和部分其他数据库的处理逻辑不一样。你的字符串里应该是混入了不可见的空字符，才触发了这个异常。

下面给你两种实用的解决方案，你可以根据业务需求选择：

方案1：清除字符串中的空字符（推荐，若空字符是无效垃圾数据） #

如果这个空字符是从外部（比如文件路径、第三方接口）意外带进来的无效数据，直接过滤掉就能解决问题。在你的C#代码里，添加参数前先处理字符串：

try {
    StringBuilder sb = new StringBuilder();
    sb.AppendLine("Insert INTo tbltemptest1 (c_value) values(@value);");
    // 精准过滤掉字符串中的空字符
    var cleanedValue = value.Replace("\0", string.Empty);
    // 如果需要更严格的清理，也可以过滤所有ASCII控制字符
    // cleanedValue = new string(value.Where(c => !char.IsControl(c)).ToArray());
    using (NpgsqlConnection con = new NpgsqlConnection(DBConnectionString)) {
        con.Open();
        using (NpgsqlCommand command = new NpgsqlCommand(sb.ToString(), con)) {
            command.Parameters.Add("@value", NpgsqlTypes.NpgsqlDbType.Varchar).Value = cleanedValue;
            command.ExecuteNonQuery();
        }
    }
} catch (Exception ex) {
    throw;
}

方案2：改用bytea类型存储（若必须保留空字符） #

如果空字符是业务上需要保留的有效数据，那你需要把列类型改成bytea（PostgreSQL的二进制存储类型），它可以存储任意字节包括0x00。具体步骤如下：

1. 先修改数据库表的列类型：

ALTER TABLE tbltemptest1 ALTER COLUMN c_value TYPE bytea;

2. 修改你的C#代码，将字符串转成字节数组，同时调整参数类型：

try {
    StringBuilder sb = new StringBuilder();
    sb.AppendLine("Insert INTo tbltemptest1 (c_value) values(@value);");
    // 将字符串转换为UTF8编码的字节数组
    var byteValue = Encoding.UTF8.GetBytes(value);
    using (NpgsqlConnection con = new NpgsqlConnection(DBConnectionString)) {
        con.Open();
        using (NpgsqlCommand command = new NpgsqlCommand(sb.ToString(), con)) {
            command.Parameters.Add("@value", NpgsqlTypes.NpgsqlDbType.Bytea).Value = byteValue;
            command.ExecuteNonQuery();
        }
    }
} catch (Exception ex) {
    throw;
}

注意：用bytea存储后，读取数据时需要把字节数组转回字符串，示例代码：Encoding.UTF8.GetString((byte[])reader["c_value"])。

内容的提问来源于stack exchange，提问作者Hardik Dobariya