Re: Dumping/Restoring with constraints?

-------------- Original message ----------------------
From: "Phoenix Kiula" <phoenix(dot)kiula(at)gmail(dot)com>
> On 8/30/08, Adrian Klaver <aklaver(at)comcast(dot)net> wrote:
> >
> > Might also be useful to do SHOW ALL from within the database cluster you have
> > on your restore machine. Will show what choices initdb made.
> >
> > test=# SHOW all;
> > <Snip>
> > lc_collate | en_US.UTF-8
> > lc_ctype | en_US.UTF-8
> > lc_messages | en_US.UTF-8
> > lc_monetary | en_US.UTF-8
> > lc_numeric | en_US.UTF-8
> > lc_time | en_US.UTF-8
> > <Snip>
>
>
>
> Thanks. I did this and LC_COLLATE etc have the values "C". How can I
> change this to UTF8?
>

You have to start from scratch. LC_COLLATE is set at the initdb stage and cannot be changed.

From the manual-

http://www.postgresql.org/docs/8.2/interactive/multibyte.html
"
Important: Although you can specify any encoding you want for a database, it is unwise to choose an encoding that is not what is expected by the locale you have selected. The LC_COLLATE and LC_CTYPE settings imply a particular encoding, and locale-dependent operations (such as sorting) are likely to misinterpret data that is in an incompatible encoding.

Since these locale settings are frozen by initdb, the apparent flexibility to use different encodings in different databases of a cluster is more theoretical than real. It is likely that these mechanisms will be revisited in future versions of PostgreSQL.

One way to use multiple encodings safely is to set the locale to C or POSIX during initdb, thus disabling any real locale awareness. "

From above the presence of a C locale should not be causing a problem. In fact when I try something similar on my machine I do not have any problems, but I am working on a Linux box. There have been previous postings dealing with locale/encoding issues on OS X. I don't know if a 'answer' was found. You might want to start a new thread 'OS X encoding/locale' to attract the attention of those that have dealt with the problem before.

--
Adrian Klaver
aklaver(at)comcast(dot)net