Hi everybody.

I have a doubt about how postgres deal with bad = encoded characters into database.

We have several gforge application. They are using = postgres as database.

If we export a database and import again, we have = to deal with several bad encoded chars. These bad chars always come from copy = & paste emails from Lotus Notes mail client. OK, I understand the Notes = client people is using is an ancient application and does not deal very well = with some Unicode chars…

What I cannot understand is why postgres accept = these bad enconded characters into database, exports them without problema but does not = allow them when importing again.

This has been happening since postgers 7.3. = However, until 7.4.XX (y don’t remember what minor version) you could import = database without ERRORs. However, since 7.4.XX it’s impossible and = it’s imperative to clean bad characters (using iconv, for example) prior = importing tables.

I agree with this postgres policy, but what I = don’t is that you can INSERT them via application. That is, no bad characters = should be inserted into database. The check should be made for both import and insert = procedures so no bad chars would appear into database.

Any suggestion / appreciation about = this?

We are using php4/5-pgsql module from several = distros (CentOS 4/5, debian 4/5 and Ubuntu 8.04LTS) so I discard a pgsql problem and = anyway database should deal with this…

Hi, > > I agree with this postgres policy, but what I don’t is that you can > INSERT them via application. That is, no bad characters should be > inserted into database. The check should be made for both import and > insert procedures so no bad chars would appear into database. > Could you please tell us which PostgreSQL version you are currently using? Also it would be useful to know: * which is your database encoding? * which is the client_encoding setting you are using? In general, what you are saying suggests me that you are using the SQL_ASCII encoding at some stage in your session (either on the server side or the client side). However, before I go on, please answer the above questions. Thanks, Gabriele

On Mon, Mar 22, 2010 at 2:48 AM, Iñigo Martinez Lasala <imartinez@vectorsf.com> wrote: > Hi everybody. > > > > I have a doubt about how postgres deal with bad encoded characters into > database. > > We have several gforge application. They are using postgres as database. > > If we export a database and import again, we have to deal with several bad > encoded chars. These bad chars always come from copy & paste emails from > Lotus Notes mail client. OK, I understand the Notes client people is using > is an ancient application and does not deal very well with some Unicode > chars… > > What I cannot understand is why postgres accept these bad enconded > characters into database, exports them without problema but does not allow > them when importing again. > > This has been happening since postgers 7.3. However, until 7.4.XX (y don’t > remember what minor version) you could import database without ERRORs. > However, since 7.4.XX it’s impossible and it’s imperative to clean bad > characters (using iconv, for example) prior importing tables. This is because postgresql's support for UTF-8 encoding (and all encoding really) has gotten tighter over time, so that the filter to catch improperly encoded UTF has gotten better with each major release.