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 ABB3F9DC839 for ; Sat, 11 Feb 2006 11:49:00 -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 44336-04 for ; Sat, 11 Feb 2006 11:49:04 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.zedxinc.com (mail.zedxinc.com [12.151.18.196]) by postgresql.org (Postfix) with ESMTP id AE2E49DC831 for ; Sat, 11 Feb 2006 11:48:54 -0400 (AST) Received: from [10.0.5.37] (scorpius.zedxinc.com [12.151.18.195]) by mail.zedxinc.com (Postfix) with ESMTP id 8FA6F2B4003; Sat, 11 Feb 2006 10:48:56 -0500 (EST) Message-ID: <43EE06F8.4020508@zedxinc.com> Date: Sat, 11 Feb 2006 10:47:04 -0500 From: Josh O'Brien User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Mario Splivalo Cc: pgsql-admin@postgresql.org Subject: Re: Pg 7.4 to 8.1 UTF problems References: <1139151996.14901.8.camel@localhost.localdomain> In-Reply-To: <1139151996.14901.8.camel@localhost.localdomain> Content-Type: multipart/mixed; boundary="------------090300010908090303070805" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.08 required=5 tests=[AWL=0.080] X-Spam-Score: 0.08 X-Spam-Level: X-Archive-Number: 200602/143 X-Sequence-Number: 20564 This is a multi-part message in MIME format. --------------090300010908090303070805 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit run this coversion command time iconv -c -f UTF8 -t UTF8 -o newdump.sql olddump.sql Josh O'Brien Mario Splivalo wrote: >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 > > --------------090300010908090303070805 Content-Type: text/x-vcard; charset=utf-8; name="obrien.vcf" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="obrien.vcf" begin:vcard fn:Joshua O'Brien n:O'Brien;Joshua org:Zedx Inc. title:Database Administrator version:2.1 end:vcard --------------090300010908090303070805--