X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 075F59DCB97 for ; Tue, 21 Mar 2006 17:38:21 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 70398-10 for ; Tue, 21 Mar 2006 17:38:22 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail19.bluewin.ch (mail19.bluewin.ch [195.186.18.65]) by postgresql.org (Postfix) with ESMTP id AE5EB9DCB74 for ; Tue, 21 Mar 2006 17:38:18 -0400 (AST) Received: from [192.168.1.74] (85.1.6.218) by mail19.bluewin.ch (Bluewin 7.2.071) id 441EA2F200070C9E; Tue, 21 Mar 2006 21:38:20 +0000 From: Ivo Rossacher To: pgsql-admin@postgresql.org Subject: Re: encoding question Date: Tue, 21 Mar 2006 22:38:03 +0100 User-Agent: KMail/1.8.2 Cc: "Ben K." References: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Message-Id: <200603212238.04111.rossacher@bluewin.ch> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200603/198 X-Sequence-Number: 20955 Am Dienstag, 21. M=E4rz 2006 21.14 schrieb Ben K.: > I just wanted to add that when I created the same database with -E > SQL_ASCII on my linux box, the dump was loaded fine. I created another > database without -E and observed the same invalid encoding problem. This is not really surprising since SQL_ASCII does not check the coding unl= ike=20 all other encodings. > > On the face value this seems to solve the problem at least superficially. The more interesting question is, what is your application doing with the n= on=20 ASCII characters within your database. The answer to this question will tel= l=20 you what the correct contents would be. > > I'd like to check the data validity, and the easiest way seems to be to > dump the data again from the linux box and compare with the original. Your application defines what is valid. Even if you know that the dump woul= d=20 be the same it would not tell you anything about the validity of the data. = So=20 the better check would be to check with the application(s) connecting to bo= th=20 servers and work with some records which do contain non ASCII characters. If both servers do give the same results with your application(s) you most= =20 possible got the coding right. > > Is there a way to compare between any two databases online? (like running > a script checking row counts and schema) If I run crc on the concat of all > fields in a row, and if the crc matches, would it be reasonably > sufficient? Is there a stronger validation method? Since any general method for comparing database contents (I don't know of s= uch=20 a tool) would use it's own drivers and setup, it will probably not get the= =20 same result as the test with your client applications. The bottom line is that only a encoding set at the server level will make=20 clear what the meaning of non ASCII characters is. The server can then deal= =20 with the conversion between the server and the client encoding so that the= =20 different clients can work even with different internal encodings. With SQL_ASCII only the client application knows. This kind of setup needs = a=20 lot of care during setup to get consistent data, especially when several=20 different applications are used. The drawback of selecting an encoding is a little performance penalty. Howe= ver=20 in my databases I could not measure any difference. I have to say here that= =20 my data does not have a lot of strings in. So it is definitly not a good te= st=20 case for this. Since there are several different clients with different=20 languages using my databases, I do use unicode as encoding. This works=20 without any problem for me. Best regards Ivo > > > Thanks. > > Ben K. > Developer > http://benix.tamu.edu > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org