pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Peter Koczan <pjkoczan@gmail.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Converting from SQL_ASCII to UTF8
Date: Thu, 29 Nov 2007 15:37:06 -0600
Message-ID: <4544e0330711291337y4c875320r36414692972296a4@mail.gmail.com> (raw)

Hi all,

I'd like to move my database encoding from SQL_ASCII to UTF8, mostly
because "No encoding conversion will be done when the setting is
SQL_ASCII. Thus, this setting is not so much a declaration that a
specific encoding is in use, as a declaration of ignorance about the
encoding." (from
http://www.postgresql.org/docs/current/static/multibyte.html) I saw a
few threads on the list regarding this before, for instance this one
(http://archives.postgresql.org/pgsql-admin/2004-01/msg00225.php) but
there's a specific issue that I'm having that wasn't addressed.

I have some UTF-8 data in my databases, and it's causing dump/restore
to fail. Specifically, I'm seeing messages like:
pg_restore: [archiver (db)] COPY failed: ERROR:  invalid byte sequence
for encoding "UTF8": 0xe14c65
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".
CONTEXT:  COPY applicants, line 282

Which happens even if I specify "-E UTF8" in the pg_dump command.

Here's the weirder part. If I just update the encoding by hand in
pg_database (as cautiously suggested by Tom Lane in the aforementioned
thread), it works. I doubt this will work in the general case, and I'd
like to at least offer this option for other people's databases.

I also tried using GNU recode (version 3.6) as suggested in similar
threads, but I got errors in both the plain and custom pg_dump
formats.

$ recode ascii..utf8 man.sql
recode: man.sql failed: Invalid input in step `ANSI_X3.4-1968..UTF-8'
$ recode ..utf8 man.sql
recode: man.sql failed: Invalid input in step `CHAR..UTF-8'

Any ideas?

Peter



view thread (2+ messages)  latest in thread

Message-ID: <4544e0330711291337y4c875320r36414692972296a4@mail.gmail.com>
Permalink:  ../4544e0330711291337y4c875320r36414692972296a4@mail.gmail.com/
Also on:    postgresql.org/message-id/4544e0330711291337y4c875320r36414692972296a4@mail.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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: pjkoczan@gmail.com
  Subject: Re: Converting from SQL_ASCII to UTF8
  In-Reply-To: <4544e0330711291337y4c875320r36414692972296a4@mail.gmail.com>
  References: <4544e0330711291337y4c875320r36414692972296a4@mail.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