Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id ECB2E2E0178 for ; Thu, 13 Mar 2008 09:59:02 -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 32533-07 for ; Thu, 13 Mar 2008 09:58:56 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ag-out-0708.google.com (ag-out-0708.google.com [72.14.246.240]) by postgresql.org (Postfix) with ESMTP id DC64E2E0078 for ; Thu, 13 Mar 2008 09:58:55 -0300 (ADT) Received: by ag-out-0708.google.com with SMTP id 23so5757412agd.5 for ; Thu, 13 Mar 2008 05:58:54 -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=YEyflxDeUV9kqXZLAzAZExvsTQdSF1Oc381RNvLvqkA=; b=Sq9A1W2XFBBQnokNN8ePty+1mdjroOyt9ryvc2crrJvFQ4979b/1PkGoD9HjqEYz16H0G18dYpSnCYp6Oal7IQK90uk4vR1TufT9BMqKkbYceLWCWmK5wwhcFOvSRUgY3A06HeXlfU/lCFxKOnyxd1BwkBCgfZB4mpywEHhR8vQ= 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=UUQpeV5Mr5jjqxKSyGV1x9Tf+uOv8H3S1qukYKTZBn0+9YrQR5GiiR57O6d0dY+mQXdbyxYXaHsOoU4F2IM4qvs859lEP6SvZFl+eEDWyQdLCDtU2ifOHVrSWsYxLTiD0B4KOkkRPtmJQ/V8m8npZQR5LwBZb/UAkTEXiUKTHiQ= Received: by 10.150.198.14 with SMTP id v14mr5376789ybf.65.1205409725167; Thu, 13 Mar 2008 05:02:05 -0700 (PDT) Received: from ?10.1.1.103? ( [84.0.171.80]) by mx.google.com with ESMTPS id j10sm17283501muh.16.2008.03.13.05.02.02 (version=TLSv1/SSLv3 cipher=RC4-MD5); Thu, 13 Mar 2008 05:02:03 -0700 (PDT) Message-ID: <47D917B7.3050106@gmail.com> Date: Thu, 13 Mar 2008 13:01:59 +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: Robert Treat , 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> <47D7DC8C.7000204@gmail.com> <200803122328.42683.xzilla@users.sourceforge.net> <47D8FB49.6000406@gmail.com> In-Reply-To: <47D8FB49.6000406@gmail.com> Content-Type: multipart/alternative; boundary="------------000902060807090804060607" 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/107 X-Sequence-Number: 28590 This is a multi-part message in MIME format. --------------000902060807090804060607 Content-Type: text/plain; charset=ISO-8859-15; format=flowed Content-Transfer-Encoding: 8bit Thanks to help from Sergey Benner, I was able to narrow the problem down. When I first initialized the PG cluster, I used the following command: initdb -D d:/postgres/8.3/data -E UTF8 -U postgres And that set my LC_COLLATE and LC_CTYPE values to 'English_United States.1252'. 'en_US-UTF-8' would be ideal for me but that doesn't seem to exist on Windows. Anyhow, when I recreated the PG cluster using this command: initdb -D ./data -E UTF8 --locale=C -U postgres It now correctly handles the values and allows me to create the unique index. I can use the C locale, but I'm a bit surprised that the default windows locale wouldn't work. Is that to be expected? Thanks, Meetesh Meetesh Karia wrote: > Thanks for your response Robert. > > Unfortunately I don't think that will work (unless I'm > misunderstanding something). > > I can't export using a client encoding of WIN1250 because I have true > UNICODE chars which can't be represented with it (I've tried and I get > the error below). Therefore, I have a sql dump with UNICODE chars and > setting the client encoding to WIN1250 when I run the sql dump script > would toast the characters. > > Additionally, here's what I get when I run your test below (my server > encoding is UTF-8): > > ltefull=# create table x (r varchar(255) unique); > NOTICE: CREATE TABLE / UNIQUE will create implicit index "x_r_key" > for table "x" > CREATE TABLE > ltefull=# > ltefull=# set client_encoding=WIN1250; > SET > ltefull=# insert into x (r) values ('Daniel Brühl'); > INSERT 0 1 > ltefull=# > ltefull=# insert into x (r) values ('Daniel Bruehl'); > ERROR: duplicate key value violates unique constraint "x_r_key" > > Meetesh > > Robert Treat wrote: >> On Wednesday 12 March 2008 09:37, Meetesh Karia wrote: >> >>> 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? >>>> >>>> >> >> I'm not convinced your problem isn't solved by proper setting of >> client_encoding for both input and output: >> >> pagila=# create table x (r varchar(255) unique); >> NOTICE: CREATE TABLE / UNIQUE will create implicit index "x_r_key" for >> table "x " >> CREATE TABLE >> pagila=# set client_encoding=WIN1250; >> SET >> pagila=# insert into x (r) values ('Daniel Brühl'); >> INSERT 0 1 >> pagila=# insert into x (r) values ('Daniel Bruehl'); >> INSERT 0 1 >> pagila=# select * from x; >> r >> --------------- >> Daniel Brühl >> Daniel Bruehl >> (2 rows) >> >> --------------000902060807090804060607 Content-Type: text/html; charset=ISO-8859-15 Content-Transfer-Encoding: 8bit Thanks to help from Sergey Benner, I was able to narrow the problem down.  When I first initialized the PG cluster, I used the following command:

initdb -D d:/postgres/8.3/data -E UTF8 -U postgres

And that set my LC_COLLATE and LC_CTYPE values to '
English_United States.1252'.  'en_US-UTF-8' would be ideal for me but that doesn't seem to exist on Windows.

Anyhow, when I recreated the PG cluster using this command:

initdb -D ./data -E UTF8 --locale=C -U postgres

It now correctly handles the values and allows me to create the unique index.  I can use the C locale, but I'm a bit surprised that the default windows locale wouldn't work.

Is that to be expected?

Thanks,
Meetesh

Meetesh Karia wrote:
Thanks for your response Robert.

Unfortunately I don't think that will work (unless I'm misunderstanding something).

I can't export using a client encoding of WIN1250 because I have true UNICODE chars which can't be represented with it (I've tried and I get the error below).  Therefore, I have a sql dump with UNICODE chars and setting the client encoding to WIN1250 when I run the sql dump script would toast the characters.

Additionally, here's what I get when I run your test below (my server encoding is UTF-8):

ltefull=# create table x (r varchar(255) unique);
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "x_r_key" for table "x"
CREATE TABLE
ltefull=#
ltefull=# set client_encoding=WIN1250;
SET
ltefull=# insert into x (r) values ('Daniel Brühl');
INSERT 0 1
ltefull=#
ltefull=# insert into x (r) values ('Daniel Bruehl');
ERROR:  duplicate key value violates unique constraint "x_r_key"

Meetesh

Robert Treat wrote:
On Wednesday 12 March 2008 09:37, Meetesh Karia wrote:
  
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?

      

I'm not convinced your problem isn't solved by proper setting of 
client_encoding for both input and output:

pagila=# create table x (r varchar(255) unique);
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "x_r_key" for 
table "x "
CREATE TABLE
pagila=# set client_encoding=WIN1250;
SET
pagila=# insert into x (r) values ('Daniel Brühl');
INSERT 0 1
pagila=# insert into x (r) values ('Daniel Bruehl');
INSERT 0 1
pagila=# select * from x;
       r
---------------
 Daniel Brühl
 Daniel Bruehl
(2 rows)

  
--------------000902060807090804060607--