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	???

Passing special (Japanese) characters to and from an external command is almost asking for trouble. Instead, I would try an ODBC connection. See: [Connect to remote PostgreSql database using Powershell](https://stackoverflow.com/a/42069324/1701026) — iRon, Mar 07 '23 at 07:05
It does not seem like PowerShell is the issue here. It is more likely your database causing that issue. Please take a look at [multibyte](https://www.postgresql.org/docs/8.1/multibyte.html) and [encoding when creating a table](https://www.postgresql.org/docs/8.1/sql-createdatabase.html). Kanji is UTF-8 but many languages are. The application needs to know that you are using Kanji characters and not Latin characters. Font and rendering are completely different. — , Mar 07 '23 at 07:41
@iRon But, I use bash to do the same thing on linux, everything is Ok. I just want to use powershell to get the same result. — Xie Steven, Mar 07 '23 at 08:41
The question marks indicate that the FONT that is being used is not compatible with the text. Use a different FONT. Is text Unicode or 8 Bit? — jdweng, Mar 07 '23 at 10:15
`$OutputEncoding` defaults to `ascii` in PowerShell 5. Run `$OutputEncoding = [System.Text.Encoding]::UTF8` to ensure `UTF8` in pipeline to `psql`. — JosefZ, Mar 07 '23 at 12:56

score 0 · Accepted Answer · answered Mar 09 '23 at 06:20

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
}

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

1 Answers1