Thread: Bad encoded chars in being inserted into database
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 -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it
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.
database encoding: UTF-8.
client_encoding: default, that is, it's no set at php level.
However, pg_client_encoding returns "UTF8" as client encoding.
Thank you, Gabriele.
-----Original Message-----
From: Gabriele Bartolini <gabriele.bartolini@2ndQuadrant.it>
To: Iñigo Martinez Lasala <imartinez@vectorsf.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Bad encoded chars in being inserted into database
Date: Mon, 22 Mar 2010 22:50:42 +0100
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
We will see if after migration this behavior has disappeared. ;-)
Thank you, Scott.
-----Original Message-----
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Iñigo Martinez Lasala <imartinez@vectorsf.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Bad encoded chars in being inserted into database
Date: Mon, 22 Mar 2010 16:05:44 -0600
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.