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 8A0E39DC85A for ; Sun, 5 Feb 2006 11:08:11 -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 25338-10 for ; Sun, 5 Feb 2006 11:08:15 -0400 (AST) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 8F8FC9DC855 for ; Sun, 5 Feb 2006 11:08:09 -0400 (AST) Received: from ls405.htnet.hr (ls405.htnet.hr [195.29.150.97]) by svr4.postgresql.org (Postfix) with ESMTP id CAED05AF09F for ; Sun, 5 Feb 2006 15:08:14 +0000 (GMT) Received: from ls401.t-com.hr (ls401.t-com.hr [195.29.150.236]) by ls405.htnet.hr (0.0.0/8.12.10) with ESMTP id k15F8ASn023491 for ; Sun, 5 Feb 2006 16:08:10 +0100 Received: from ls401.t-com.hr (localhost.localdomain [127.0.0.1]) by ls401.t-com.hr (Qmlai) with ESMTP id 7357E70003 for ; Sun, 5 Feb 2006 16:08:10 +0100 (CET) X-Envelope-Sender: msplival@jagor.srce.hr Received: from 83-131-54-45.adsl.net.t-com.hr (83-131-54-45.adsl.net.t-com.hr [83.131.54.45]) by ls401.t-com.hr (Qmlai) with ESMTP id 2F41FA8002 for ; Sun, 5 Feb 2006 16:08:10 +0100 (CET) Subject: Pg 7.4 to 8.1 UTF problems From: Mario Splivalo To: pgsql-admin@postgresql.org Content-Type: text/plain Date: Sun, 05 Feb 2006 16:06:35 +0100 Message-Id: <1139151996.14901.8.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.4.1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120] X-Spam-Score: 0.12 X-Spam-Level: X-Archive-Number: 200602/120 X-Sequence-Number: 20541 Hello. I have a database created with -E UTF-8. I need to transfer it to pg8.1.2. First I dumped the database: pg_dump -v -E UTF-8 the_database > the_database.psql Then I created the database on pg8.1.2 createdb the_database -E UTF-8 Then, I wanted to insert the dump: psql -f the_database.psql the_database But, psql complained with: psql:the_database.psql:86: ERROR: invalid UTF-8 byte sequence detected near byte 0x8d CONTEXT: COPY _netsms, line 1367, column text: "Padamo u pozu 69.uz \uffffmas mog macana u usta.a ja tvoju ljepu picu" Now, here it says \ufff, and I couldn't do copy/paste from gnome-terminal directly into evolution, I needed first to paste it into gedit, and then copy from there and paste here. Neverthenless, here is what hexdump says: mario@mike:~/work/lidija$ cat lidija.psql | grep "amo u pozu 69" | head -1 | hexdump -C 00000000 32 30 30 36 2d 30 31 2d 30 31 20 32 31 3a 33 30 |2006-01-01 21:30| 00000010 3a 30 39 2e 38 34 36 35 34 36 30 30 09 2b 33 38 |:09.84654600.+38| 00000020 35 39 38 32 39 37 35 38 32 09 50 61 64 61 6d 6f | 598297582.Padamo| 00000030 20 75 20 70 6f 7a 75 20 36 39 2e 75 7a 8d 6d 61 | u pozu 69.uz.ma| 00000040 73 20 6d 6f 67 20 6d 61 63 61 6e 61 20 75 20 75 |s mog macana u u| 00000050 73 74 61 2e 61 20 6a 61 20 74 76 6f 6a 75 20 6c |sta.a ja tvoju l| 00000060 6a 65 70 75 20 70 69 63 75 0a |jepu picu.| 0000006a From here it is visible that the troubled charachter has has ASCII HEX code 8d, which is regular ascii charachter. If I create the database (on 8.1.2) with -E SQL_ASCII, the import goes ok. If I run psql on 7.4 server, everything works ok. I haven't tried on 8.0, because I don't have them. How could I import the database to 8.1.2, but so that database on 8.1.2 is with -E UTF8? Mike -- "I can do it quick, I can do it cheap, I can do it well. Pick any two." Mario Splivalo msplival@jagor.srce.hr