Skip site navigation (1) Skip section navigation (2)

Re: Unable to restore dump due to client encoding issues -- or, when is SQL_ASCII really UTF8

From: Bill Moran <wmoran(at)collaborativefusion(dot)com>
To: Michael Fuhr <mike(at)fuhr(dot)org>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Unable to restore dump due to client encoding issues -- or, when is SQL_ASCII really UTF8
Date: 2007-02-27 15:31:47
Message-ID: 20070227103147.08c21db8.wmoran@collaborativefusion.com (view raw or flat)
Thread:
Lists: pgsql-general
In response to Michael Fuhr <mike(at)fuhr(dot)org>:

[snip]

> > The beginning of the dump file I am restoring has the following:
> > --
> > -- PostgreSQL database dump
> > --
> > 
> > SET client_encoding = 'SQL_ASCII';
> > [...]
> > 
> > But when I try to pull the dump in with psql, I get the following errors:
> > ERROR:  invalid byte sequence for encoding "UTF8": 0xa0
> > HINT:  This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".
> > 
> > Connecting to the database and issuing "show client_encoding" shows that
> > the database is indeed set to SQL_ASCII.
> 
> client_encoding doesn't show the database encoding, it shows the
> client encoding; execute "show server_encoding" to see the database
> encoding.  You can also use "psql -l" or "\l" from within psql to
> see all databases and their encodings.  The error suggests that the
> database encoding is UTF8.

Ahh ... see, I knew there'd be something obvious I was missing, but all
the google searches I tried were focused on client encoding, so I wasn't
getting this answer.

The database was, indeed, UTF8, which is the default on newer Postgres.
I find it odd that I can't alter the encoding after database creation,
but in this case it wasn't a big deal, as it's a lab system and I can
just back up a few steps and start over.

Good to learn this change now, as opposed to during the next production
deployment :)

> > Now ... I'm expecting the server to accept any byte sequence, since we're
> > using SQL_ANSII, but that is (obviously) not the case.  Am I missing
> > something obvious here?  Grepping the entire dump file shows absolutely
> > no references to UTF8 ... so why is the server trying to validate the
> > byte string as UTF8?
> 
> Probably because the database is UTF8 (see above).  Either create
> the database as SQL_ASCII (see createdb's -E option) or change the
> client_encoding setting in the dump to whatever the encoding really
> is (probably LATIN1 or WIN1252 for Western European languages).

Yup.  Thanks for the quick response!

-- 
Bill Moran
Collaborative Fusion Inc.

In response to

Responses

pgsql-general by date

Next:From: David GagnonDate: 2007-02-27 15:37:24
Subject: select ARRAY[1,4,3] @> ARRAY[3,1] gives ERROR: operator does not exist: integer[] @> integer[] ???
Previous:From: George NychisDate: 2007-02-27 15:05:13
Subject: Re: performance of partitioning?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group