X-Original-To: pgsql-jdbc-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id BD62CD1B17B;
	Sat, 17 Jul 2004 20:17:09 -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 10165-07; Sat, 17 Jul 2004 23:17:09 +0000 (GMT)
Received: from mail.svhost.com.ar (mail.svhost.com.ar [200.80.239.22])
	by svr1.postgresql.org (Postfix) with ESMTP id 48D3BD1B178;
	Sat, 17 Jul 2004 20:17: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 9C5101DE13; Sat, 17 Jul 2004 20:15:24 -0300 (ART)
Message-ID: <40F9B599.6080205@sistemat.com.ar>
Date: Sat, 17 Jul 2004 20:26:17 -0300
From: "Dario V. Fassi" <software@sistemat.com.ar>
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: Tom Lane <tgl@sss.pgh.pa.us>,
	Oliver Jowett <oliver@opencloud.com>, pgsql-sql@postgresql.org,
	"pgsql-jdbc@postgresql.org" <pgsql-jdbc@postgresql.org>
Subject: Re: [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE
References: <40F98613.1060405@sistemat.com.ar>
 <16994.1090099096@sss.pgh.pa.us>
In-Reply-To: <16994.1090099096@sss.pgh.pa.us>
Content-Type: multipart/alternative;
	boundary="------------020204070704000302080203"
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/221
X-Sequence-Number: 10605

This is a multi-part message in MIME format.
--------------020204070704000302080203
Content-Type: text/plain; charset=us-ascii; format=flowed
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,


--------------020204070704000302080203
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
  <title></title>
</head>
<body text="#000000" bgcolor="#ffffff">
Tom Lane wrote:<br>
<blockquote type="cite" cite="mid16994.1090099096@sss.pgh.pa.us">
  <pre wrap="">"Dario V. Fassi" <a class="moz-txt-link-rfc2396E" href="mailto:software@sistemat.com.ar">&lt;software@sistemat.com.ar&gt;</a> writes:
  </pre>
  <blockquote type="cite">
    <pre wrap="">A simple question, we need to migrate many (&gt;20) postgres databases from 
SQL_ASCII encoding to UNICODE encoding, over a 7.3.6 server.
    </pre>
  </blockquote>
  <pre wrap=""><!---->
SQL_ASCII is not an encoding (it's more like the absence of knowledge
about an encoding).  What is the data actually stored as?
  </pre>
  <blockquote type="cite">
    <pre wrap="">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.
    </pre>
  </blockquote>
  <pre wrap=""><!---->
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.
  </pre>
</blockquote>
<div class="moz-signature"><br>
Yes you are right , the original data come from a DB2 with CodePage
IBM-850&nbsp; and was inserted&nbsp; without complains in a Postgres 7.3.6 with
SQL_ASCII.<br>
<br>
Now we are in a Jail , because IBM-850 , isn't WIN, isn't&nbsp; ISO-xx ,
isn't no one postgresql's encoding. <br>
So when in change via pg_databases the encoding , 8 bits characters
become garbage.<br>
More even if we accept this garbage chars and we set encoding to e.g.
ISO-8859-1&nbsp;&nbsp; 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).<br>
<br>
We are in a big problem, and the only way out I can imagine is fix the
data by hand&nbsp; <span class="moz-smiley-s13"><span> :-! </span></span>.<br>
<br>
Dario,<br>
<br>
</div>
</body>
</html>

--------------020204070704000302080203--