Can not insert German characters in Postgres

Question

I am using UTF8 as encoding for my Postgres 8.4.11 database:

CREATE DATABASE test
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = mydata
       LC_COLLATE = 'de_DE.UTF-8'
       LC_CTYPE = 'de_DE.UTF-8'
       CONNECTION LIMIT = -1;

ALTER DATABASE test SET default_tablespace='mydata';
ALTER DATABASE test SET temp_tablespaces=mydata;

And the output of \l

 test | postgres  | UTF8     | de_DE.UTF-8 | de_DE.UTF-8 |

When I try to insert a German character:

create table x(a text);

insert into x values('ä,ß,ö');

ERROR:  invalid byte sequence for encoding "UTF8": 0xe42cdf
HINT:  This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".

I am using puTTY to connect. Any idea?

Erwin Brandstetter · Accepted Answer · 2021-10-12T13:39:17.720

8

The key element is the client_encoding - the encoding the server expects from your client. It has to match what is actually sent. What do you get for show client_encoding? Is it UNICODE?

Read more in the chapter Automatic Character Set Conversion Between Server and Client of the manual.

If you are using psql as client, you can set client_encoding with \encoding. Check the encoding your local system users (on Linux type locale in the shell) and set a matching client_encoding in psql. You can avoid such complications if you use the same locale on your system as you use as encoding for your PostgreSQL server.

If you use puTTY (on Windows), make sure to set its "Translation" accordingly. Have a look at Settings: Window - Translation. Must match client_encoding. You can right-click in a running session and chose Change Settings. You can also save these settings with your saved sessions.

edited Oct 12 '21 at 13:39

answered Jul 11 '12 at 10:45

Erwin Brandstetter

605,456
145
1,078
1,228

`test=# show client_encoding; client_encoding ----------------- UTF8 (1 row)` – baboso Jul 11 '12 at 11:39
'UTF8' should be good, `UNICODE` is just an alias here. Now you have to make sure `UTF8` matches your client. I suspect you'll have to adjust to another encoding. – Erwin Brandstetter Jul 11 '12 at 11:48
@baboso: "0xe42cdf" -- this looks like you are entering characters through putty using latin1 encoding. Either set your Linux environment to "de_DE.ISO-8859-1" or configure PuTTY to send UTF-8 characters. – araqnid Jul 11 '12 at 12:13
running `"C:\Program Files\PSQL\bin\psql.exe" \encoding UTF8` is asking for password for user `UTF8` ... how to change the client encoding when the system does not have postgreqsl installed? but is accesing remote database – PresleyDias Oct 04 '12 at 07:23
@PresleyDias: Look for `encoding` in [this manual page](http://www.postgresql.org/docs/current/interactive/app-psql.html), especially for the chapter "Notes for Windows Users". – Erwin Brandstetter Oct 04 '12 at 13:59
Using `\encoding UTF8` or `SET client_encoding TO 'UTF8'` did correct output from `psql`, but I still couldn't insert German characters. I finally had to set the `LANG` shell variable to `de_DE.UTF-8` before starting `psql`, which did the trick (I normally use `LANGUAGE=en_US.UTF-8` locale because I prefer english dialogues, but had to change `LANG` to force `psql` to accept german umlauts). So I start `psql` this way: `LANG=de_DE.UTF-8 psql database_name` – LBC Nov 20 '16 at 15:00

Can not insert German characters in Postgres

1 Answers1

Linked

Related