Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 4606E2E2D9B for ; Wed, 12 Mar 2008 10:37:40 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 04972-03 for ; Wed, 12 Mar 2008 10:37:25 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.159]) by postgresql.org (Postfix) with ESMTP id D27462E2D92 for ; Wed, 12 Mar 2008 10:37:24 -0300 (ADT) Received: by fg-out-1718.google.com with SMTP id 22so2436253fge.23 for ; Wed, 12 Mar 2008 06:37:22 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:reply-to:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type; bh=//RrlK4AFZudQx2obUurNlqyniy3YGahSMagExLFlYE=; b=WE4Fn5BM8miZP1if3Ed/OzfoVEc3yBGijp99QBygHqjRPiwkio0CXn19TrKF0cvFNLgwEQVUDMZMUAwdcN9xkA8qmtlwoy8//KJEIHKRYx5zSOOm+K/npWsVhCipkPn+bJeDkpvYgNI7JaVN8lS56+rgy8m5RaV++vU93kYYA4M= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:reply-to:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type; b=NCb04gaFuPt5v4WK8l7NcjcwaqKK4DGqPP+BYy2Nk2plih2h4UYRFyYzqeQ8zu1Rj0xpnSA6rZwxhoA8TQ4IWJY4AJbLIEUzM7310UXwdzCHqPXQSFm4TIczrxLxaewrp+ixET/mBMr0aNaZ9q8byFrslRW39XVo7hfcqhjKcdc= Received: by 10.86.93.17 with SMTP id q17mr9218546fgb.18.1205329042494; Wed, 12 Mar 2008 06:37:22 -0700 (PDT) Received: from ?10.1.1.103? ( [84.0.151.235]) by mx.google.com with ESMTPS id 12sm12487290fgg.6.2008.03.12.06.37.20 (version=TLSv1/SSLv3 cipher=RC4-MD5); Wed, 12 Mar 2008 06:37:21 -0700 (PDT) Message-ID: <47D7DC8C.7000204@gmail.com> Date: Wed, 12 Mar 2008 14:37:16 +0100 From: Meetesh Karia Reply-To: meetesh.karia@alumni.duke.edu User-Agent: Thunderbird 2.0.0.12 (Windows/20080213) MIME-Version: 1.0 To: meetesh.karia@alumni.duke.edu CC: pgsql-admin@postgresql.org Subject: Re: Encoding problems with migration from 8.0.14 to 8.3.0 on Windows References: <47D7CBAA.4090308@gmail.com> In-Reply-To: <47D7CBAA.4090308@gmail.com> Content-Type: multipart/alternative; boundary="------------030500020400030208000103" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200803/97 X-Sequence-Number: 28580 This is a multi-part message in MIME format. --------------030500020400030208000103 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit One quick addition to this: The column I'm creating this unique index on is a varchar(255) and the command I was running was: create unique index foo_name on foo (name); If I use the following, it now works: create unique index foo_name on foo (cast(name as bytea)); Thoughts? Meetesh Meetesh Karia wrote: > Hi all, > > I'm trying to migrate from 8.0.14 on Windows (Vista Home Premium) to > 8.3.0 and I've been trying to solve what appears to be an encoding > problem. My old db was in the UNICODE encoding. I know that this > isn't supported on 8.0.x, but it was a restore of a db from a Linux > environment and postgres didn't appear to have any problems with it. > > My 8.3 server and client encodings are UTF8 and I used pg_dumpall (I > tried the 8.0 and 8.3 versions) to dump the db. However, when I tried > to restore the db, I got an error during index creation which wouldn't > let me create a unique index on a column that had all unique values > (it had the index in 8.0 and a group by having query with no indexes > on the table confirms uniqueness). The thing that this column does > have however is values like: > > 'Bruehl' > 'Brühl' > > I created a blank table with the unique index on it and inserted rows > one at a time until I confirmed that it was the above values that were > causing a problem. Running the following query shows the difference > in the hex encoded values (I changed my client encoding to WIN1250 to > get the below to show up correctly): > > select name, encode(decode(name, 'escape'), 'hex') from ... > > name | encode > ---------------+---------------------------- > Daniel Brühl | 44616e69656c204272c3bc686c > Daniel Bruehl | 44616e69656c2042727565686c > (2 rows) > > I've also tried exporting using an encoding of WIN1250 but I get > errors like this: > > pg_dump: Error message from server: ERROR: character 0xc383 of > encoding "UNICODE" has no equivalent in "WIN1250" > > Anyone have any thoughts or suggestions? Why would the index creation > fail? Is there a workaround? > > Thanks, > Meetesh --------------030500020400030208000103 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit One quick addition to this:

The column I'm creating this unique index on is a varchar(255) and the command I was running was:

create unique index foo_name on foo (name);

If I use the following, it now works:

create unique index foo_name on foo (cast(name as bytea));

Thoughts?

Meetesh

Meetesh Karia wrote:
Hi all,

I'm trying to migrate from 8.0.14 on Windows (Vista Home Premium) to 8.3.0 and I've been trying to solve what appears to be an encoding problem.  My old db was in the UNICODE encoding.  I know that this isn't supported on 8.0.x, but it was a restore of a db from a Linux environment and postgres didn't appear to have any problems with it.

My 8.3 server and client encodings are UTF8 and I used pg_dumpall (I tried the 8.0 and 8.3 versions) to dump the db.  However, when I tried to restore the db, I got an error during index creation which wouldn't let me create a unique index on a column that had all unique values (it had the index in 8.0 and a group by having query with no indexes on the table confirms uniqueness).  The thing that this column does have however is values like:

'Bruehl'
'Brühl'

I created a blank table with the unique index on it and inserted rows one at a time until I confirmed that it was the above values that were causing a problem.  Running the following query shows the difference in the hex encoded values (I changed my client encoding to WIN1250 to get the below to show up correctly):

select name, encode(decode(name, 'escape'), 'hex') from ...

     name      |           encode
---------------+----------------------------
 Daniel Brühl  | 44616e69656c204272c3bc686c
 Daniel Bruehl | 44616e69656c2042727565686c
(2 rows)

I've also tried exporting using an encoding of WIN1250 but I get errors like this:

pg_dump: Error message from server: ERROR:  character 0xc383 of encoding "UNICODE" has no equivalent in "WIN1250"

Anyone have any thoughts or suggestions?  Why would the index creation fail?  Is there a workaround?

Thanks,
Meetesh
--------------030500020400030208000103--