Pg 7.4 to 8.1 UTF problems

From: Mario Splivalo <msplival(at)jagor(dot)srce(dot)hr>
To: pgsql-admin(at)postgresql(dot)org
Subject: Pg 7.4 to 8.1 UTF problems
Date: 2006-02-05 15:06:35
Message-ID: 1139151996.14901.8.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-admin

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(at)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(at)jagor(dot)srce(dot)hr

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Stephen Frost 2006-02-05 15:51:56 Re: [HACKERS] Postgres 8.1.x and MIT Kerberos 5
Previous Message Magnus Hagander 2006-02-05 12:14:38 Re: Postgres 8.1.x and MIT Kerberos 5