X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id DD718D1B38E for ; Sat, 17 Jul 2004 20:25:06 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 13179-05 for ; Sat, 17 Jul 2004 23:25:07 +0000 (GMT) Received: from mail.svhost.com.ar (mail.svhost.com.ar [200.80.239.22]) by svr1.postgresql.org (Postfix) with ESMTP id A0D92D1B392 for ; Sat, 17 Jul 2004 20:25:03 -0300 (ADT) Received: from sistemat.com.ar (devel.sistemat.com.ar [192.168.1.40]) by mail.svhost.com.ar (Postfix) with ESMTP id 383B02451C for ; Sat, 17 Jul 2004 20:23:21 -0300 (ART) Message-ID: <40F9B776.9050601@sistemat.com.ar> Date: Sat, 17 Jul 2004 20:34:14 -0300 From: "Dario V. Fassi" Organization: SISTEMATICA ingenieria de software srl User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 (ax) X-Accept-Language: es, es-ar, en-us, en MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Re: Migrate postgres databases from SQL_ASCII to UNICODE Content-Type: multipart/alternative; boundary="------------080807060306090705010005" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.5 tagged_above=0.0 required=5.0 tests=HTML_MESSAGE, HTML_TITLE_EMPTY X-Spam-Level: X-Archive-Number: 200407/202 X-Sequence-Number: 14270 This is a multi-part message in MIME format. --------------080807060306090705010005 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Tom Lane wrote: >"Dario V. Fassi" writes: > > >>A simple question, we need to migrate many (>20) postgres databases from >>SQL_ASCII encoding to UNICODE encoding, over a 7.3.6 server. >> >> > >SQL_ASCII is not an encoding (it's more like the absence of knowledge >about an encoding). What is the data actually stored as? > > >>With Dump/Restore , we get an error (Invalid Unicode) in any field that >>has a 8 bits character coming from the SQL_ASCII , even setting the >>client_encoding to WIN, ISO-8859-1, and others encodings. >> >> > >It might work to just UPDATE pg_database to set datencoding to the >correct value reflecting what you have actually stored. You might then >need to REINDEX any indexes on textual columns, but I don't think >anything else would go wrong. > >If you have a mishmash of different encodings in a single database, then >of course there is no simple solution; you are in for some pain while >you try to fix the data. > > Yes you are right , the original data come from a DB2 with CodePage IBM-850 and was inserted without complains in a Postgres 7.3.6 with SQL_ASCII. Now we are in a Jail , because IBM-850 , isn't WIN, isn't ISO-xx , isn't no one postgresql's encoding. So when in change via pg_databases the encoding , 8 bits characters become garbage. More even if we accept this garbage chars and we set encoding to e.g. ISO-8859-1 it's impossible go to a UNICODE because this garbage chars are invalid in client's encoding , so they are reject (in translation process as invalid unicode chars). We are in a big problem, and the only way out I can imagine is fix the data by hand :-! . Dario, --------------080807060306090705010005 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Tom Lane wrote:
"Dario V. Fassi" <software@sistemat.com.ar> writes:
  
A simple question, we need to migrate many (>20) postgres databases from 
SQL_ASCII encoding to UNICODE encoding, over a 7.3.6 server.
    

SQL_ASCII is not an encoding (it's more like the absence of knowledge
about an encoding).  What is the data actually stored as?
  
With  Dump/Restore , we get an error (Invalid Unicode) in any field that 
has a 8 bits character coming from the SQL_ASCII , even setting the 
client_encoding to WIN, ISO-8859-1,  and others encodings.
    

It might work to just UPDATE pg_database to set datencoding to the
correct value reflecting what you have actually stored.  You might then
need to REINDEX any indexes on textual columns, but I don't think
anything else would go wrong.

If you have a mishmash of different encodings in a single database, then
of course there is no simple solution; you are in for some pain while
you try to fix the data.
  

Yes you are right , the original data come from a DB2 with CodePage IBM-850  and was inserted  without complains in a Postgres 7.3.6 with SQL_ASCII.

Now we are in a Jail , because IBM-850 , isn't WIN, isn't  ISO-xx , isn't no one postgresql's encoding.
So when in change via pg_databases the encoding , 8 bits characters become garbage.
More even if we accept this garbage chars and we set encoding to e.g. ISO-8859-1   it's impossible go to a UNICODE because this garbage chars are invalid in client's encoding , so they are reject (in translation process as invalid unicode chars).

We are in a big problem, and the only way out I can imagine is fix the data by hand  :-! .

Dario,

--------------080807060306090705010005--