How to resolve garbled code when using powershell to execute psql and the Copy command?

Question

I'm writing powershell script to copy "csv" from client machine to remote postgre database table with psql copy command.

The copy command executed successfully, but some Japanese characters have become garbled.

I have set the encoding for client and console stdin and stdout, but garbled code still occurred.

[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
}

For example, the csv(utf8 encoding) content looks like the following

header1,header2
1001,その他

When copied to postgre, I use pgAdmin to view the table data. It shows:

header1	header2
1001	???

Answer 1

I resolved my question. I confused the Postgre SQL COPY command with psql \COPY instruction.

Actually, I need to use \COPY instruction to execute copy command, then I can directly copy from file instead of writing to stdin.

[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 = & psql -h 10.0.xxx.xx -d db1 -U user1 -w -c "\COPY $table FROM $($file.FullName) WITH(FORMAT CSV,HEADER TRUE, ENCODING 'UTF8')"
    $Result
}