Postgresql: set default psql client encoding

Question

When I connect to a Postgresql DB using psql, I often get these messages:

=> SELECT * FROM question_view ;
ERROR: character with byte sequence 0xd7 0x9e in encoding "UTF8" has no equivalent in encoding "LATIN1"

Following this SO answer, I understand that I should change the client encoding accordingly:

SET client_encoding = 'UTF8';

Changing the client_encoding every time I connect to the DB is cumbersome. Is there a way to permanently configure this setting, either in the .pgpass file or anywhere else?

Answer 1

Is there a way to permanently configure this setting, either in the .pgpass file or anywhere else

Yes there is: it's ~/.psqlrc (or %APPDATA%\postgresql\psqlrc.conf in Windows)

See the manual for details: http://www.postgresql.org/docs/current/static/app-psql.html#AEN88713

• Adam Matan: Adding SET client_encoding = 'UTF8'; to ~/.psqlrc did the trick. Thanks!
• Adam Mackler: This doesn't seem to work when (re)connecting from within a psql session, for example after restarting the server.
• Alkanshel: I had to create this file in Windows and then set a user environment var called PSQLRC pointing to that directory. No more complaints, yay

Answer 2

I was facing the same error but changing from ANSI(X64) to Unicode(x64) in my ODBC conn fixed my issue.