See psql reference page notes for windows users for details
“If there is one thing developers like less than writing documentation, it’s responding to unnecessary escalations […] and too many escalations wear down the developers.”
Tom Limoncelli
Site Reliability Engineering Manager at Stack Overflow
“We needed a better place to store all the questions and answers that people were repeatedly asking, and we discovered Stack Overflow for Teams.”
Suyog Rao
Director of Engineering at Elastic Cloud
“We use Stack Overflow for Teams internally when onboarding new developers, and when new developers ask questions, everyone can benefit from the shared knowledge.”
Roberta Arcoverde
Principal Software Developer at Stack Overflow
Teams
Connect and share knowledge within a single location that is structured and easy to search.
PostgreSQL: Warning: Console code page (437) differs from Windows code page (1252)
Using PostgreSQL when I connect to a db using \c testdb inside PostgreSQL Database SQL Prompt. I successfully connect to the db but getting the following warning:
What does this warning mean? How to resolve it?
13 Answers 13
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.
The default codepage for CMD.exe is different than the default for postgres. To change for CMD.exe using the REGISTRY try this:
Then reopen CMD.exe
To make it even more obvious, the file to which @user3423801 is adding the line
is in the scripts directory where you installed Postgre.
For example, in my case it is
Go to Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Command Processor
New a string value named: Autorun and change the value to be chcp 1252
Or you can simply type cmd.exe /c chcp 1252 in the Command Prompt window.
The answer of dvdgsng is correct but with code example is more obviously.
you just go to the power-shell or cmd.exe and type the command chcp 1252 or whatever the page number it wants «the one that is the windows code page». If the problem still persists, just open the console properties ‘By clicking the power shell icon on the top left of the console window and choosing properties from the drop-down menu’ and change the font to «Lucida Console». It worked for me, But you have to open power-shell as an Administrator.
After much digging for an answer that made sense to me, I found this help email chain at the PostgreSQL site which basically says to run chcp 1252 from inside an open command window. I was then able to run my PostgreSQL commands without the code warning.
NOTE: this change does not persist so you have to run it every time you open a new command window where you plan to use PostgreSQL commands.
I couldn’t figure out how to set it for Cygwin globally. This seemed to work though in my bash script
The answers above are okay, but don’t mention anywhere that Windows 1252 encoding is good for English language versions of Windows AND the other Western European Languages. This completes the answer for those people who may get confused about the aforementioned application to German language encoding. Yes it works for English without umlauts and other special characters needed for German, Spanish, French, Italian, Romanian, Hungarian, etc.
What is Windows 1252 encoding?
Windows-1252 or CP-1252 (code page 1252) is a single-byte character encoding of the Latin alphabet, used by default in the legacy components of Microsoft Windows for English and some other Western languages (other languages use different default encodings).
“If there is one thing developers like less than writing documentation, it’s responding to unnecessary escalations […] and too many escalations wear down the developers.”
Tom Limoncelli
Site Reliability Engineering Manager at Stack Overflow
“We needed a better place to store all the questions and answers that people were repeatedly asking, and we discovered Stack Overflow for Teams.”
Suyog Rao
Director of Engineering at Elastic Cloud
“We use Stack Overflow for Teams internally when onboarding new developers, and when new developers ask questions, everyone can benefit from the shared knowledge.”
Roberta Arcoverde
Principal Software Developer at Stack Overflow
Teams
Connect and share knowledge within a single location that is structured and easy to search.
psql saying database does not exist but it does exist in pgadmin
I have just installed Postgres v10.4 on Windows and created, using pgadmin, a new database called analysis. It is there, I can see it in pgadmin, and it has one table in it. However, I cannot connect to this database using psql.
I don’t know what I’m doing with psql, but all I’ve found is that I can connect to db postgres as user postgres. There’s a \l command and it shows 3 databases (2 of which, template0 and template1) I can’t even see in pgadmin.
How can I connect to the database (analysis) that I’ve just created in pgadmin? Can anybody explain what user I’m logged in under in pgadmin, if it’s different to ‘postgres’ and if it isn’t, how is it that I can’t see in psql what I can see in pgadmin?
1 Answer 1
template0 and template1 are know as skeleton databases. When you use CREATE DATABASE command postgres copy the existing databae. By default template1 database is used to create new database.
I think your pgadmin and psql are connected to different cluster. A cluster in postgresql is collection of one or more databases in a single instance of server
The image you posted does not show the complete view of pgadmin browser. But if there are 2 cluster then you can see in pgadmin like «Servers(2)». To find running port of each cluster right click on corresponding cluster and select properties then click connection tab. Here port number can be seen. Then connect to cluster in psql using that Port.
See psql reference page notes for windows users for details
Можно-ли как-нибудь поменять кодировку консоли ну или как-то это исправить, если причина в другом?
Можно: SET CLIENT_ENCODING TO
Спасибо, но не помогло, это
Спасибо, но не помогло, это уже пробовал) Все равно иероглифы вместо русских букв.
Не ту кодировку выбрали вот и
Не ту кодировку выбрали вот и всё. Всем помогает, вам нет?
Дело в том, что она не
Дело в том, что она не меняется, как была 866, так и остается, может я куда-то не туда ввожу команду? (пробовал в саму консоль и в граф. интерфейсе, команда выполняется (за
Так в psql надо команду
Так в psql надо команду вводить, при чём здесь консоль?
Команда переключает вывод символов PostgreSQL в ту кодировку, которую вы укажете.
При этом на содержимое БД это никак не влияет.
Странно, может это все руки,
Странно, может это все руки, поступаю так:
Запускаем PSQL Shell (psql), после ввода названия сервера, порта, БД, пользователя и пароля..
music1=# SET CLIENT_ENCODING TO ‘WIN1251’;
(выводятся по прежнему кракозябры)
Еще пробовал
music1=# \encoding win1251; (тоже нет)
и менять строку #client_encoding = sql_ascii (в postgresql.conf) на #client_encoding = win1251
(все как раньше) Хотя результаты запросов, если их вводить в pgAdmin (кнопка «выполнить пользовательские sql-запросы»), выводятся нормально.
Простите, а вы что не в
Простите, а вы что не в курсе, что консоль Windows работает в кодировке CP866? (кодировке DOS, а не Windows. Это из-за русских имён файлов сложилось по исторической причине)
Чтобы не создавать новую тему, добавлю вопрос сюда
Так все таки, как сделать, чтобы в консоли psql были русские буквы? У мене не получается.
По порядку рассказываю, что я делаю.
1. Система Windows XP. Кодирока консоли 866.
2. Во время инсталяции на вопрос про «Locale» я оставил [Default locale].
3. Пробую войти в консоль psql. После ввода server, database, port, username, пароль я получаю:
4. Нашел этот «Notes for Windows users» (здесь), там вообще написано то, что вводить нужно не в консоль psql, а в виндовскую cmd:
Set the code page by entering cmd.exe /c chcp 1252.
Команда cmd.exe /c chcp 1252 явно не для консоли psql. Значит ответ из официального мануала в данном случае не решает проблему.
5. Прочитав эту ветку пробую сначала посмотреть какая кодировка стотит в psql изначально (я еще ничего не менял):
Пробовал обратно ставить WIN1251, пробовал UTF8. Ничего не помогает, кракозябры и все.
Шрифт консоли переключал с «Точечные шрифты» на «Lucinda Console». Тоже не помогает.
Что я делаю не так? Как его «русифицировать»?
Спасибо.
SET CLIENT_ENCODING
SET CLIENT_ENCODING устанавливает кодировку для вывода значений из БД, а не хелпа.
Воспользуйтесь рекомендацией
Воспользуйтесь рекомендацией «Set the code page by entering cmd.exe /c chcp 1252.»
т.е. запускаете cmd.exe, набираете «chcp 1251«, далее enter. Выйдет сообщение
Текущая кодовая страница: 1251
После этого запускаете psql.
Кодовую страницу необходимо устанавливать при каждом запуске cmd.
“If there is one thing developers like less than writing documentation, it’s responding to unnecessary escalations […] and too many escalations wear down the developers.”
Tom Limoncelli
Site Reliability Engineering Manager at Stack Overflow
“We needed a better place to store all the questions and answers that people were repeatedly asking, and we discovered Stack Overflow for Teams.”
Suyog Rao
Director of Engineering at Elastic Cloud
“We use Stack Overflow for Teams internally when onboarding new developers, and when new developers ask questions, everyone can benefit from the shared knowledge.”
Roberta Arcoverde
Principal Software Developer at Stack Overflow
Teams
Connect and share knowledge within a single location that is structured and easy to search.
How to configure postgresql for the first time?
I have just installed postgresql and I specified password x during installation. When I try to do createdb and specify any password I get the message:
createdb: could not connect to database postgres: FATAL: password authentication failed for user
How should I start? Can I add myself as a user to the database?
10 Answers 10
The other answers were not completely satisfying to me. Here’s what worked for postgresql-9.1 on Xubuntu 12.04.1 LTS.
Connect to the default database with user postgres:
Set the password for user postgres, then exit psql (Ctrl-D):
ALTER USER postgres with encrypted password ‘xxxxxxx’;
Edit the pg_hba.conf file:
sudo vim /etc/postgresql/9.1/main/pg_hba.conf
and change «peer» to «md5» on the line concerning postgres:
Restart the database :
Create a user having the same name as you (to find it, you can type whoami ):
Beware of passwords: it will first ask you twice the new password (for the new user), repeated, and then once the postgres password (the one specified on step 2).
Again, edit the pg_hba.conf file (see step 3 above), and change «peer» to «md5» on the line concerning «all» other users:
Now createdb should work.
Under Linux PostgresQL is usually configured to allow the root user to login as the postgres superuser postgres from the shell (console or ssh).
Then you would just create a new database as usual:
This is my solution:
There are two methods you can use. Both require creating a user and a database.
Using createuser and createdb,
Using the SQL administration commands, and connecting with a password over TCP
And, then in the psql shell
Then you can login,
If you don’t know the port, you can always get it by running the following, as the postgres user,
Sidenote: the postgres user
I suggest NOT modifying the postgres user.
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:
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.
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
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.
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.
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.
Fwd: 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.
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.
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»
Re: warning about console code page on starting psql
On Tuesday, December 16, 2014, David Johnston wrote:
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.
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.
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\
postgres=# \c testdb2
You are now connected to database «testdb2» as user «postgres».
testdb2=# select * from jedstblinmelsdb;
key | name | address
——+——+———
(0 rows)