pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedBad encoded chars in being inserted into database
5+ messages / 3 participants
[nested] [flat]
* Bad encoded chars in being inserted into database
@ 2010-03-22 08:48 Iñigo Martinez Lasala <imartinez@vectorsf.com>
0 siblings, 2 replies; 5+ messages in thread
From: Iñigo Martinez Lasala @ 2010-03-22 08:48 UTC (permalink / raw)
To: pgsql-admin
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…
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Bad encoded chars in being inserted into database
@ 2010-03-22 21:50 Gabriele Bartolini <gabriele.bartolini@2ndQuadrant.it>
parent: Iñigo Martinez Lasala <imartinez@vectorsf.com>
1 sibling, 1 reply; 5+ messages in thread
From: Gabriele Bartolini @ 2010-03-22 21:50 UTC (permalink / raw)
To: Iñigo Martinez Lasala <imartinez@vectorsf.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Bad encoded chars in being inserted into database
@ 2010-03-22 22:05 Scott Marlowe <scott.marlowe@gmail.com>
parent: Iñigo Martinez Lasala <imartinez@vectorsf.com>
1 sibling, 1 reply; 5+ messages in thread
From: Scott Marlowe @ 2010-03-22 22:05 UTC (permalink / raw)
To: Iñigo Martinez Lasala <imartinez@vectorsf.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Bad encoded chars in being inserted into database
@ 2010-03-23 08:10 Iñigo Martinez Lasala <imartinez@vectorsf.com>
parent: Gabriele Bartolini <gabriele.bartolini@2ndQuadrant.it>
0 siblings, 0 replies; 5+ messages in thread
From: Iñigo Martinez Lasala @ 2010-03-23 08:10 UTC (permalink / raw)
To: Gabriele Bartolini <gabriele.bartolini@2ndQuadrant.it>; +Cc: pgsql-admin
Postgresql version: 8.1.13 (8.1.13-0etch1)
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Bad encoded chars in being inserted into database
@ 2010-03-23 08:11 Iñigo Martinez Lasala <imartinez@vectorsf.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Iñigo Martinez Lasala @ 2010-03-23 08:11 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-admin
We are working with 8.1 and migrating to 8.4....
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-03-23 08:11 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-03-22 08:48 Bad encoded chars in being inserted into database Iñigo Martinez Lasala <imartinez@vectorsf.com>
2010-03-22 21:50 ` Gabriele Bartolini <gabriele.bartolini@2ndQuadrant.it>
2010-03-23 08:10 ` Iñigo Martinez Lasala <imartinez@vectorsf.com>
2010-03-22 22:05 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-03-23 08:11 ` Iñigo Martinez Lasala <imartinez@vectorsf.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox