Search code examples
postgresqlpowershellutf-8psqlazure-automation

Psql used with Azure runbook can't handle UTF-8 encoding

The setup is like this, we have an Azure runbook which is using a hybrid worker with psql to insert data in to a CosmosDB running PostgreSQL.

The issue is that the data contains Swedish characters like å,ä & ö and when the data is inserted into the DB these characters convert to a ?-sign instead so "Fjäril" would become "Fj?ril" instead.

If I log on to the VM, the hybrid worker is running on with the same account and run the exact same code below with the exact same data, db, schema and table then this doesn't happen and I can see "åäö" in the database.

"INSERT INTO $Schema.$table 
 VALUES('$Guid','$Givenname','$Username','$Surname','$Name');"  | & "$psqlpath\psql" --csv $dburl -c

I have read about others having issues with similar things like in this article: https://medium.com/@mozzeph/fix-utf-8-encoding-when-calling-azure-child-runbooks-inline-78644e6af242

But I'm not using any child runbooks for this, I have tried making a separate test-runbook that just acquires the data and then runs the code above with the same result.

If I write out the data with Write-Output just before adding to the DB then I can see "åäö" being output to the logs correctly.

So far I have tried:

  • Checking that the DB is using UTF-8, which it is: enter image description here
  • Tried setting the encoding in the psql-command using "SET client_encoding = 'UTF8';
  • Tried setting the system locale to UTF-8 in the control panel

enter image description here

Solution

  • I found the reason, apparently Powershell ISE automatically sets the format to UTF-8 while an ordinary Powershell session doesn't so my testing in PS ISE worked but the PS Session started from an Azure Runbook needed to set the format manually first with: $OutputEncoding = [System.Text.UTF8Encoding]::new()