使用C#从SQL Server迁移至PostgreSQL v15时遇Unicode编码转换错误求助

错误原因 #

\uDDDD 属于UTF-16的未配对代理字符，SQL Server允许存储这类字符，但你的PostgreSQL数据库虽然编码是UTF8，但Collation和Character type设置为English_United States 1252（对应Windows-1252单字节编码），数据写入时会尝试将Unicode字符转码到1252编码，而代理字符不在1252编码范围内，因此触发报错。

解决方案 #

1. 过滤数据中的无效代理字符 #

如果这些代理字符是无效垃圾数据，可在C#读取SQL Server数据时直接过滤：

• 遍历字符移除代理项：

  public static string CleanSurrogateCharacters(string input)
  {
      if (string.IsNullOrWhiteSpace(input)) return input;
      var cleanedBuilder = new StringBuilder(input.Length);
      foreach (char c in input)
      {
          if (!char.IsSurrogate(c))
          {
              cleanedBuilder.Append(c);
          }
      }
      return cleanedBuilder.ToString();
  }
  

• 用正则表达式批量替换：

  using System.Text.RegularExpressions;
  
  string cleanedText = Regex.Replace(rawTextFromSqlServer, @"[\uD800-\uDFFF]", string.Empty);
  

2. 修改PostgreSQL的字符排序规则 #

如果需要保留所有合法Unicode字符，需将PostgreSQL的Collation和Character type改为支持完整Unicode的选项（如en_US.UTF-8）：

• 修改现有列的排序规则（无需重建数据库）：

  ALTER TABLE your_target_table 
  ALTER COLUMN your_text_column TYPE TEXT COLLATE "en_US.UTF-8";
  

• 若重建数据库，创建时指定正确配置：

  CREATE DATABASE your_new_db 
  WITH ENCODING 'UTF8' 
       LC_COLLATE 'en_US.UTF-8' 
       LC_CTYPE 'en_US.UTF-8';
  

3. 确保PostgreSQL客户端连接编码正确 #

在C#的Npgsql连接字符串中明确指定UTF8编码，避免客户端自动选择不兼容的编码：

Server=your_postgres_host;Port=5432;Database=your_db;User Id=your_user;Password=your_pass;Encoding=UTF8;

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