PostgreSQL: encoding problems on Windows when using psql command line utility

Question

I'm working in a centralized monitoring system on Windows 2008 R2, I have installed a PostgreSQL 9.3 to use psql from the command line.

When I try to access to some remote Postgres (an 8.4 in my principal case) I have an error with the encoding:

command:

psql.exe -h 192.168.114.12 -p 5432 -d db_seros_transaccion -U postgres -f script.sql

error:

psql: FATAL:  la conversión entre WIN1252 y LATIN1 no está soportada

I try adding the sentence

SET client_encoding = 'UTF8';

in my script but the problem persist (and with other encodings too, like LATIN1 & WIN1252).

After googling it I found people that update some rows in the server to make the connection, and this is a problem to me.

Can anyone help me to make a connection using psql without an update? Is it possible?

Have you tried the suggested solution in this thread, i.e. running chcp 65001 before psql? — Milen A. Radev
– Milen A. Radev, Commented Jan 6, 2014 at 15:40
Yes, after make the question, try with chcp 65001, 437, 1252 to test. not work. now I trying to use sqlrun throw an odbc (in a minutes the results ;P) — Frank N Stein
– Frank N Stein, Commented Jan 6, 2014 at 15:51
In addition to chcp 65001 to set a unicode codepage you may also need to tell psql it's in a unicode terminal during startup. You can't SET client_encoding later, because you need an already-working connection for that. Try also running SET PGCLIENTENCODING=utf-8 (after the chcp 65001) before starting psql and report back on that. — Craig Ringer
– Craig Ringer, Commented Jan 7, 2014 at 0:40
Thanks a lot Craig Ringer, works, finally works! You are my new idool now! The steps are: 1. open the cmd 2. SET PGCLIENTENCODING=utf-8 3. chcp 65001 4. psql -h 192.168.114.12 -U postgres — Frank N Stein
– Frank N Stein, Commented Jan 7, 2014 at 15:13

das-g · Accepted Answer · 2021-03-25 10:39:32Z

99

Thanks a lot Craig Ringer, works, finally works! You are my new idool now!

The steps are:

open the cmd
SET PGCLIENTENCODING=utf-8
chcp 65001
psql -h your.ip.addr.ess -U postgres

edited Mar 25, 2021 at 10:39

das-g

10.1k6 gold badges44 silver badges95 bronze badges

answered Jan 9, 2014 at 14:32

Frank N Stein

2,5983 gold badges24 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Feillen Over a year ago

I recently came across a related issue and this has helped me to troubleshoot through it a bit. I just wanted to make a little note to it: server and client encoding do matter and I could only successfully psql to my remote server when my PGCLIENTENCODING was set to iso8859-1 and I had run chcp 1252. I assume this was because I have my server encoding set to latin1 (a.k.a. iso8859-1). So: always mind about encodings!

Frank N Stein Over a year ago

Hi, Feillen, yes, and more. In this days I have to migrate some scripts from 2008 to 2012. And the enconding problems came back, some scripts that works fine on 2008 need change the enconding or something like that.

mpour Over a year ago

What does line 4 do? I think get 1,2,3. But I am not sure about 4!!

Frank N Stein Over a year ago

Hi DT, yes, works on Win10 ( my test: pasteboard.co/HZ1uvgy.png )

Laurent GRENIER Over a year ago

Just want to add a remark I've just figured out. Never use PowerShell. Encoding the dump works like a charm on the standard shell but not at all with PowerShell. The file keeps encoded with UTF-16 in my case.

KUL · Accepted Answer · 2022-04-21 03:01:12Z

4

Windows 10 / Windows server 2016 or later:

Open Windows Control Panel
Select Region (and Language)
Click Change system locale
Beta: Use Unicode UTF-8 for worldwide language support
Click OK

or

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage]
"ACP"="65001"
"OEMCP"="65001"
"MACCP"="65001"

The PowerShell console and CMD will display Cyrillic correctly

answered Apr 21, 2022 at 3:01

KUL

5315 silver badges17 bronze badges

1 Comment

joe blogs Over a year ago

legend this works like a treat

Falchio · Accepted Answer · 2022-08-01 07:32:57Z

1

As an option, try enter this command in psql :

postgres=# \! chcp 65001

65001 is equals UTF-8
1251 = WIN 1251

answered Aug 1, 2022 at 7:32

Falchio

7308 silver badges21 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: encoding problems on Windows when using psql command line utility

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related