无法在Postgres中插入德文字符 [英] Can not insert German characters in Postgres

问题描述

  CREATE DATABASE test 
 WITH OWNER = postgres 
 ENCODING ='UTF8'
 TABLESPACE = mydata 
 LC_COLLATE ='de_DE.UTF-8'
 LC_CTYPE ='de_DE.UTF-8'
连接限制= -1; 
 
 ALTER DATABASE测试SET default_tablespace ='mydata'; 
 ALTER DATABASE测试SET temp_tablespaces = mydata; 
  

而输出的 \l / p>

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

当我尝试插入一个德文字符：

 创建表x（一个文本）; 
 
 insert into x values（'ä，ß，ö'）; 
  

 错误：无效的字节序列用于编码UTF8：0xe42cdf 
提示：如果字节序列与由server_encoding控制的服务器所期望的编码不匹配，也可能会发生此错误。 
  

我正在使用puTTY连接。任何想法？

解决方案

关键要素是 client_encoding - 编码服务器期望从您的客户端。它必须匹配实际发送的内容。 显示client_encoding ？是否 UNICODE ？

阅读更多内容服务器和客户端之间的自动字符集转换。

如果您使用 psql 作为客户端，可以使用 \encoding 设置 client_encoding 。检查您本地系统使用的编码 - 在shell上的Linux类型 locale 上，并尝试在psql中设置匹配的 client_encoding 。如果在您的系统上使用与您的PostgreSQL服务器的 encoding 一样的区域设置，则可以避免此类复杂情况。

如果您使用 puTTY ，请确保相应地设置其翻译。看看设置：窗口 - 翻译。必须匹配 client_encoding 。您可以在正在运行的会话中右键单击并选择更改设置。您还可以使用保存的会话保存这些设置。

我在Windows XP系统上的puTTY有UTF-8， encoding 和 client_encoding 全部设置为 UNICODE （= UTF8 在Postgres），并且工作正常。

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?

解决方案

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 uses - on Linux type locale in the shell - and try setting 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, make sure to set its "Translation" accordingly. Have a look at the 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.

I have "UTF-8" for puTTY on a Windows XP system, encoding and client_encoding all set to UNICODE (= UTF8 in Postgres), and that works just fine.

这篇关于无法在Postgres中插入德文字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！