使用PowerShell执行psql COPY命令时日文乱码如何解决？

问题背景 #

使用PowerShell脚本通过psql的COPY命令将本地UTF-8编码的CSV文件导入远程PostgreSQL数据库，命令执行成功，但日文字符导入后显示为???。已设置控制台输入输出编码为UTF-8，问题仍未解决。

原脚本 #

[System.Console]::OutputEncoding = [Text.UTF8Encoding]::UTF8
[System.Console]::InputEncoding = [Text.UTF8Encoding]::UTF8
$env:PGPASSWORD = 'pwd';
foreach($file in Get-ChildItem .\Import -Filter *.csv){
    $table = $file.BaseName
    $Truncate_Sql = "truncate table $table;"
    $Result = & psql -h 10.0.xxx.xx -d db1 -U user1 -w -c $Truncate_Sql
    Write-Host $Result  $table  "Ok."
    $Result =   Write-Output (Get-Content -Path $file.FullName -Encoding utf8) | & psql -h 10.0.xxx.xx -d db1 -U user1 -w -c "set client_encoding=UTF8;COPY $table FROM STDIN WITH(FORMAT CSV,HEADER TRUE, ENCODING 'UTF8')"
    $Result
}

示例CSV（UTF-8编码） #

header1,header2
1001,その他

解决方案 #

1. 关键修改点 #

• 添加PGCLIENTENCODING环境变量，强制psql客户端使用UTF-8编码
• 使用Get-Content -Raw读取整个文件，避免逐行处理导致的编码转换问题
• 移除多余的Write-Output，减少字符串处理环节

修改后的脚本 #

[System.Console]::OutputEncoding = [System.Text.UTF8Encoding]::UTF8
[System.Console]::InputEncoding = [System.Text.UTF8Encoding]::UTF8
$env:PGPASSWORD = 'pwd'
$env:PGCLIENTENCODING = 'UTF8' # 强制psql客户端编码为UTF-8

foreach($file in Get-ChildItem .\Import -Filter *.csv){
    $table = $file.BaseName
    # 清空目标表
    $Truncate_Sql = "truncate table $table;"
    $Result = & psql -h 10.0.xxx.xx -d db1 -U user1 -w -c $Truncate_Sql
    Write-Host "$Result $table Ok."

    # 导入CSV数据到数据库
    $Result = Get-Content -Path $file.FullName -Encoding utf8 -Raw | 
              & psql -h 10.0.xxx.xx -d db1 -U user1 -w -c "COPY $table FROM STDIN WITH(FORMAT CSV, HEADER TRUE, ENCODING 'UTF8')"
    $Result
}

2. 额外检查项 #

• 确认数据库及表的字符集：执行SHOW server_encoding;确保服务器编码为UTF-8；目标表字段需使用text或varchar类型，且表的字符集为UTF-8
• 验证CSV文件编码：确保文件确实是UTF-8编码（无BOM也可，PostgreSQL支持）

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