Notes for windows users psql

\encoding win-1251

SET client_encoding=win866;

������:
���������: 688

postgres=# \encoding win-1251
postgres=# select * from «Test»;
name
———-
╨�����╓╔
John

postgres=# SET client_encoding=win866;
SET
postgres=# select * from «Test»;
name
———-
���������
Rom

Источник

PostgreSQL — Кириллица в psql под Windows

В статье пойдёт речь о том, как добиться корректного вывода кириллицы в «консоли» Windows ( cmd.exe ).

Содержание

Описание проблемы

В дистрибутив PostgreSQL, помимо всего прочего, для работы с СУБД входит:

• приложение с графическим интерфейсом pgAdmin ;
• консольная утилита psql .

При работе с psql в среде Windows пользователи всегда довольно часто сталкиваются с проблемой вывода кириллицы. Например, при отображении результатов запроса к таблице, в полях которых хранятся строковые данные на русском языке.

Ну и зачем тогда работать с psql , кому нужно долбить клавиатурой в консольке, когда можно всё сделать красиво и быстро в pgAdmin ? Ну, не всегда pgAdmin доступен, особенно если речь идёт об удалённой машине. Кроме того, выполнение SQL-запросов в текстовом режиме консоли — это +10 к хакирству.

Решение проблемы

• MS Windows 7 SP1 x64;
• PostgreSQL 8.4.12 x32.

На сервере имеется БД, созданная в кодировке UTF8.

Суть проблемы в том, что cmd.exe работает (и так будет до скончания времён) в кодировке CP866 , а сама Windows — в WIN1251 , о чём psql предупреждает при начале работы:

Значит, надо как-то добиться, чтобы кодировка была одна.

В разных источниках встречаются разные рецепты, включая правку реестра и подмену файлов в системных папках Windows. Ничего этого делать не нужно, достаточно всего трёх шагов:

1. сменить шрифт у cmd.exe ;
2. сменить текущую кодовую страницу cmd.exe ;
3. сменить кодировку на стороне клиента в psql .

Конкретные действия

Супер быстро и просто

Запускаете cmd.exe , оттуда psql :

Быстро и просто

Запускаете cmd.exe , оттуда psql :

Вводите пароль (если установлен) и выполняете команду:

И всё. Теперь результаты запроса, содержащие кириллицу, будут отображаться нормально. Но есть небольшой косяк:

Потому предлагаем ещё способ, который этого недостатка лишён.

Посложнее и подольше

Запустить cmd.exe , нажать мышью в правом левом верхнем углу окна, там Свойства — Шрифт — выбрать Lucida Console. Нажать ОК.

Кстати, обратите внимание — теперь предупреждения о несовпадении кодировок нет.

Всё, теперь кириллица будет нормально отображаться.

Источник

warning about console code page on starting psql

warning about console code page on starting psql

SUMMARY OF PROBLEM: Postgres notes no errors upon installation, but upon startup of psql there’s a warning; documentation fix doesn’t eliminate the warning message.

1. psql says this when I log in:

Password for user postgres:

WARNING: Console code page (437) differs from Windows code page (1252)

8-bit characters might not work correctly. See psql reference

page «Notes for Windows users» for details.

Type «help» for help.

2. psql ref. pg. says this:

Notes for Windows Users

psql is built as a «console application». Since the Windows console windows use a different encoding than the rest of the system, you must take special care when using 8-bit characters within psql. If psql detects a problematic console code page, it will warn you at startup. To change the console code page, two things are necessary:

• Set the code page by entering cmd.exe /c chcp 1252 . (1252 is a code page that is appropriate for German; replace it with your value.) If you are using Cygwin, you can put this command in /etc/profile .

Set the console font to Lucida Console , because the raster font does not work with the ANSI code page.

3. When I enter «cmd.exe /c chcp 1252» into the command prompt, it says this:

Microsoft Windows [Version 6.1.7600]

Copyright (c) 2009 Microsoft Corporation. All rights reserved.

C:\Users\zzzzzz>cmd.exe /c chcp 1252

Active code page: 1252

4. Am I supposed to type this at the command prompt: «cmd.exe /c chcp 437»? Because that’s what I did, and now the command prompt says this.

C:\Users\zzzzzz>cmd.exe /c chcp 437

Active code page: 437

. but I quit and reopened psql, the error message isn’t gone or changed.

5. The instructions to set console font mean nothing to me: «Set the console font to Lucida Console , because the raster font does not work with the ANSI code page». Googled it and found nothing that made sense to me. Set whose console font? PG or Windows? Where and how to set it? What is a console, anyway? Googled again and again, finally discovered that the console being referred to is apparently psql itself. By clicking on the little rectangular icon at the top left of the title bar of the command prompt (psql), Properties, Font. there it is. «Raster Fonts» had been selected. I changed this to Lucida Console. Quit psql and restarted it.

6. psql startup warning has now changed to a different font, no other change.

7. I changed the console page back to 1252 in the command prompt:

Microsoft Windows [Version 6.1.7600]

Copyright (c) 2009 Microsoft Corporation. All rights reserved.

C:\>cmd.exe /c chcp 1252

Active code page: 1252

8. Quit and restarted psql (twice). The warning message has not changed. Is it possible that pg is wrongly detecting code page 437, and does it matter? I have a lot of work to do and want a sparkling clean foundation to start from so I don’t have problems later.

This email has been checked for viruses by Avast antivirus software.
www.avast.com

Re: warning about console code page on starting psql

Set the code page by entering cmd.exe /c chcp 1252 . (1252 is a code page that is appropriate for German; replace it with your value.) If you are using Cygwin, you can put this command in /etc/profile .

Do not type the «cmd.exe /c» part. This will run a new instance of «cmd» (the Windows command-line console); «\c» means «run the following command». So you are basically starting a new console, changing the code page within that new console, and exiting back to your original console.

Instead, once you have a command prompt, use «chcp 1252» to change the code page of the Windows console to match the code page «psql» expects from Windows. Then you can run «psql» without the warning. You will have to do this each time you open a new command line console, before running «psql», as far as I can tell.

Re: warning about console code page on starting psql

Going from memory here but.

The window is which the command prompt appears is called the console. When you type psql at the command prompt you are now running a console application in that same console. You can have more than one running console and each one is independent. Any changes to the console, e.g. via the «cmd.exe /c chcp 1252» command, only persist as long as that console window remains open. Going into and out of a console application should not make a difference, only closing the actual window. Same goes for the font — it is a property of the console and not psql directly.

Since the database server uses the Windows code page you want your console to match. So given said warning you want to supply the current Windows value to the chcp command.

The message and documentation could probably be improved though I’m not positive what I am saying is 100% correct as I have never encountered this problem — because I’ve never used psql on Windows.

Re: warning about console code page on starting psql

On 12/16/2014 11:10 PM, Scott Robertson wrote:
> Set the code page by entering cmd.exe /c chcp 1252. (1252 is a code
> page that is appropriate for German; replace it with your value.) If
> you are using Cygwin, you can put this command in /etc/profile.

Do not type the «cmd.exe /c» part. This will run a new instance of «cmd»
(the Windows command-line console); «\c» means «run the following
command». So you are basically starting a new console, changing the
code page within that new console, and exiting back to your original
console.

Instead, once you have a command prompt, use «chcp 1252» to change the
code page of the Windows console to match the code page «psql» expects
from Windows. Then you can run «psql» without the warning. You will have
to do this each time you open a new command line console, before running
«psql», as far as I can tell.

So basically the documented fix is useless. At least a «/k» would keep the new console with altered code page around for interactive use but the /c switch simply kills the console after running a single command whose sole purpose is to change the to-be-killed console’s codepage.

It is implied that since you are chasing the console codepage that the target value would be whatever Window’s is using. but that could be made more clear as well.

Encodings are confusing and this doesn’t even mention using windows psql against Linux or how Unicode would fit in — though external notes indicate that the font change suggestion also solves Unicode characters as well as dealing with ANSI.

Fwd: warning about console code page on starting psql

———- Forwarded message ———-
From: David G Johnston [via PostgreSQL]
Date: Tuesday, December 16, 2014
Subject: Re: warning about console code page on starting psql
To: David G Johnston

On 12/16/2014 11:10 PM, Scott Robertson wrote:
> Set the code page by entering cmd.exe /c chcp 1252. (1252 is a code
> page that is appropriate for German; replace it with your value.) If
> you are using Cygwin, you can put this command in /etc/profile.

Do not type the «cmd.exe /c» part. This will run a new instance of «cmd»
(the Windows command-line console); «\c» means «run the following
command». So you are basically starting a new console, changing the
code page within that new console, and exiting back to your original
console.

Instead, once you have a command prompt, use «chcp 1252» to change the
code page of the Windows console to match the code page «psql» expects
from Windows. Then you can run «psql» without the warning. You will have
to do this each time you open a new command line console, before running
«psql», as far as I can tell.

So basically the documented fix is useless. At least a «/k» would keep the new console with altered code page around for interactive use but the /c switch simply kills the console after running a single command whose sole purpose is to change the to-be-killed console’s codepage.

It is implied that since you are chasing the console codepage that the target value would be whatever Window’s is using. but that could be made more clear as well.

Encodings are confusing and this doesn’t even mention using windows psql against Linux or how Unicode would fit in — though external notes indicate that the font change suggestion also solves Unicode characters as well as dealing with ANSI.

Re: warning about console code page on starting psql

———- Forwarded message ———-
From: David G Johnston [via PostgreSQL]
Date: Tuesday, December 16, 2014
Subject: Re: warning about console code page on starting psql
To: David G Johnston

On 12/16/2014 11:10 PM, Scott Robertson wrote:
> Set the code page by entering cmd.exe /c chcp 1252. (1252 is a code
> page that is appropriate for German; replace it with your value.) If
> you are using Cygwin, you can put this command in /etc/profile.

Do not type the «cmd.exe /c» part. This will run a new instance of «cmd»
(the Windows command-line console); «\c» means «run the following
command». So you are basically starting a new console, changing the
code page within that new console, and exiting back to your original
console.

Instead, once you have a command prompt, use «chcp 1252» to change the
code page of the Windows console to match the code page «psql» expects
from Windows. Then you can run «psql» without the warning. You will have
to do this each time you open a new command line console, before running
«psql», as far as I can tell.

So basically the documented fix is useless. At least a «/k» would keep the new console with altered code page around for interactive use but the /c switch simply kills the console after running a single command whose sole purpose is to change the to-be-killed console’s codepage.

It is implied that since you are chasing the console codepage that the target value would be whatever Window’s is using. but that could be made more clear as well.

Encodings are confusing and this doesn’t even mention using windows psql against Linux or how Unicode would fit in — though external notes indicate that the font change suggestion also solves Unicode characters as well as dealing with ANSI.

Re: warning about console code page on starting psql

Thanks David, I am starting to understand the console is what they used
to call a dos prompt, the little black window where psql appears.

As you say, the font change had no effect on the Windows command prompt
console that I open from the Windows start menu. However, the font
change did persist in psql. Each time I reopen psql, it is now
automatically in Lucida Console font.

It seems I should be able to get to C:\> from the same (psql) console,
but don’t know how, or how to get back to psql if I did. And don’t know
if there’s any reason to.

It sounds like you’re saying that I should type this command in the psql
console each time I open it. In which case, I’d have to get to the C:\>
in order to do it, since it’s not a psql command.

However, returning to my original post, I have already determined that
Windows is set to 1252 as (?) instructed by pg documentation, and is
still giving the same error. Is there a windows configuration file I
should edit or.

Re: warning about console code page on starting psql

Thanks David, I am starting to understand the console is what they used
to call a dos prompt, the little black window where psql appears.

As you say, the font change had no effect on the Windows command prompt
console that I open from the Windows start menu. However, the font
change did persist in psql. Each time I reopen psql, it is now
automatically in Lucida Console font.

It seems I should be able to get to C:\> from the same (psql) console,
but don’t know how, or how to get back to psql if I did. And don’t know
if there’s any reason to.

It sounds like you’re saying that I should type this command in the psql
console each time I open it. In which case, I’d have to get to the C:\>
in order to do it, since it’s not a psql command.

Yes, I had a shortcut for psql in the program files start menu of Windows, which I moved to the desktop. I don’t know another way to open it.

Shortcut target is «C:\Program Files\PostgreSQL\9.4\scripts\runpsql.bat»
Start in location is «C:\Program Files\PostgreSQL\9.4\scripts»

However, returning to my original post, I have already determined that
Windows is set to 1252 as (?) instructed by pg documentation, and is
still giving the same error. Is there a windows configuration file I
should edit or.

This email has been checked for viruses by Avast antivirus software.
www.avast.com

Re: warning about console code page on starting psql

Sorry, I forgot to Cc the list earlier when I was replying from my phone.

On 18 December 2014 at 07:38, Scott Robertson wrote:

I assume you also changed the font. Since I don’t currently use
Postgres and have never used it on Windows, I’m not sure how important
it is to change the font, but since it was mentioned in the docs, I
assume it should be done.

> C:\>cd \program files\postgresql\9.4\bin\
>
> C:\Program Files\PostgreSQL\9.4\bin>psql
> Password:
> psql: FATAL: password authentication failed for user «Luther»
>
> C:\Program Files\PostgreSQL\9.4\bin>psql
> Password:
> psql: FATAL: password authentication failed for user «Luther»

The way you were running psql before seems to be via a .bat or .cmd
file. Did the authentication work that way? If so, it might be easiest
to edit that file to add in the «chcp 1252» command before the call to
the actual psql command. Or otherwise see how that file calls the
actual psql command and do the same thing manually.

If you decide to edit the .bat (or .cmd) file, make a backup of it first
in case you make a mistake.

Re: warning about console code page on starting psql

On Tuesday, December 16, 2014, David Johnston wrote:

———- Forwarded message ———-
From: David G Johnston [via PostgreSQL]
Date: Tuesday, December 16, 2014
Subject: Re: warning about console code page on starting psql
To: David G Johnston

On 12/16/2014 11:10 PM, Scott Robertson wrote:
> Set the code page by entering cmd.exe /c chcp 1252. (1252 is a code
> page that is appropriate for German; replace it with your value.) If
> you are using Cygwin, you can put this command in /etc/profile.

Do not type the «cmd.exe /c» part. This will run a new instance of «cmd»
(the Windows command-line console); «\c» means «run the following
command». So you are basically starting a new console, changing the
code page within that new console, and exiting back to your original
console.

Instead, once you have a command prompt, use «chcp 1252» to change the
code page of the Windows console to match the code page «psql» expects
from Windows. Then you can run «psql» without the warning. You will have
to do this each time you open a new command line console, before running
«psql», as far as I can tell.

So basically the documented fix is useless. At least a «/k» would keep the new console with altered code page around for interactive use but the /c switch simply kills the console after running a single command whose sole purpose is to change the to-be-killed console’s codepage.

It is implied that since you are chasing the console codepage that the target value would be whatever Window’s is using. but that could be made more clear as well.

Encodings are confusing and this doesn’t even mention using windows psql against Linux or how Unicode would fit in — though external notes indicate that the font change suggestion also solves Unicode characters as well as dealing with ANSI.

Case closed and much learned. The documentation was depending on me remembering some things learned from a dos class almost 20 years ago, haven’t used the command line console since.

SUMMARY OF SOLUTION
start console cmd.exe
use icon upper left on title bar to change font to Lucida Console
change to root directory: cd\
check the code page to confirm it’s wrong: chcp
change the code page: chcp 1252
change to postgresql directory; in my Windows 7 it’s: cd program files\postgresql\9.4\bin\
open psql (in same console) as superuser so operating system user password isn’t expected: psql -U postgres
prompt comes up for postgres password

RESULTS:
Microsoft Windows [Version 6.1.7600]

C:\>chcp
Active code page: 437

C:\>chcp 1252
Active code page: 1252

C:\>cd program files\postgresql\9.4\bin\

C:\Program Files\PostgreSQL\9.4\bin>
C:\Program Files\PostgreSQL\9.4\bin>psql -U postgres
Password for user postgres:
psql (9.4rc1)
Type «help» for help.

postgres=# \c testdb2
You are now connected to database «testdb2» as user «postgres».
testdb2=# select * from jedstblinmelsdb;
key | name | address
——+——+———
(0 rows)

Источник