Re: Can I change the character encoding for a DB?

Hi Tom,

Thanks for the info and warning. Fortunately this is just a development
database so any problems won't be too big a deal.

Does dumping and re-creating the DB avoid such a potential problem?

I read the info written by Tasuo, but I have to admit that the whole
encoding thing is still full of mystery for me. For example, if the database
has a SQL_ASCII encoding and you enter japanese text, what encoding is used?
Does it depend on the client used to enter it? I wonder if it is possible
that there is some SJIS data in my DB... My understanding is that the DB
doesn't ensure that data makes sense in the set encoding, it just allows the
db to make conversions when dealing with clients such as the ODBC and JDBC
drivers.

Anyway, in this case the base data came from a EUC_JP encoded DB, but a VB
app (and some psql scripts) has been used to enter and modify data. It uses
the ODBC driver provided for postgres, everything had been working smoothly
and I only noticed a problem when I tried getting data using java and the
jdbc driver. I created a new mini test DB with the EUC_JP encoding and the
data was returned correctly.

regards,
Iain

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Iain" <iain(at)mst(dot)co(dot)jp>
Cc: <pgsql-admin(at)postgresql(dot)org>
Sent: Tuesday, January 27, 2004 2:05 AM
Subject: Re: [ADMIN] Can I change the character encoding for a DB?

> "Iain" <iain(at)mst(dot)co(dot)jp> writes:
> > I created a DB with SQL_ASCII encoding but I want to change it to
EUC_JP.
> > Can I do this without creating a new database?
>
> If you're certain that the data already in the database is in EUC_JP
> encoding, it would probably work to do a quick UPDATE on the
> pg_database.encoding entry for the database (and then start fresh
> backend sessions, since they won't notice otherwise). If there's data in
> there that is not correct per EUC_JP then this will create big problems,
> so don't say I didn't warn you ...
>
> regards, tom lane