pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Meetesh Karia <meetesh.karia@gmail.com>
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
Date: Wed, 12 Mar 2008 14:37:16 +0100
Message-ID: <47D7DC8C.7000204@gmail.com> (raw)
In-Reply-To: <47D7CBAA.4090308@gmail.com>
References: <47D7CBAA.4090308@gmail.com>

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

view thread (8+ messages)  latest in thread

Message-ID: <47D7DC8C.7000204@gmail.com>
Permalink:  ../47D7DC8C.7000204@gmail.com/
Also on:    postgresql.org/message-id/47D7DC8C.7000204@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: meetesh.karia@gmail.com, meetesh.karia@alumni.duke.edu
  Subject: Re: Encoding problems with migration from 8.0.14 to 8.3.0 on Windows
  In-Reply-To: <47D7DC8C.7000204@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox