Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 5CC672E0245 for ; Thu, 13 Mar 2008 00:29:42 -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 08380-07 for ; Thu, 13 Mar 2008 00:29:39 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from vms044pub.verizon.net (vms044pub.verizon.net [206.46.252.44]) by postgresql.org (Postfix) with ESMTP id 7D6532E008C for ; Thu, 13 Mar 2008 00:29:39 -0300 (ADT) Received: from new-host.home ([71.245.161.199]) by vms044.mailsrvcs.net (Sun Java System Messaging Server 6.2-6.01 (built Apr 3 2006)) with ESMTPA id <0JXN00B0DGDCDBA1@vms044.mailsrvcs.net> for pgsql-admin@postgresql.org; Wed, 12 Mar 2008 22:29:39 -0500 (CDT) Date: Wed, 12 Mar 2008 23:28:42 -0400 From: Robert Treat Subject: Re: Encoding problems with migration from 8.0.14 to 8.3.0 on Windows In-reply-to: <47D7DC8C.7000204@gmail.com> To: pgsql-admin@postgresql.org, meetesh.karia@alumni.duke.edu Message-id: <200803122328.42683.xzilla@users.sourceforge.net> MIME-version: 1.0 Content-type: text/plain; charset=iso-8859-15 Content-transfer-encoding: quoted-printable Content-disposition: inline References: <47D7CBAA.4090308@gmail.com> <47D7DC8C.7000204@gmail.com> User-Agent: KMail/1.9.1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/101 X-Sequence-Number: 28584 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=FChl' > > > > 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=FChl | 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=20 client_encoding for both input and output: pagila=3D# create table x (r varchar(255) unique); NOTICE: CREATE TABLE / UNIQUE will create implicit index "x_r_key" for=20 table "x " CREATE TABLE pagila=3D# set client_encoding=3DWIN1250; SET pagila=3D# insert into x (r) values ('Daniel Br=FChl'); INSERT 0 1 pagila=3D# insert into x (r) values ('Daniel Bruehl'); INSERT 0 1 pagila=3D# select * from x; r =2D-------------- Daniel Br=FChl Daniel Bruehl (2 rows) =2D-=20 Robert Treat Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL