Re: encoding and LC_COLLATE

On Monday, November 14, 2011 3:03:32 am LPlateAndy wrote:
> Hi,
>
> I set up my postgres 9.0 install 6 months ago and generally everything is
> fine but a recent data load with an e acute character failed which an
> unsupported message which surprised me as we're using UTF-8.
>
> However, i can now see that the listing for the database set up show a
> restriction under LC_COLLATE and LC_CTYPE to the UK which would explain the
> blocking of this character. Oddly, this is set even if i only specify UTF-8
> which i guess means that it is set against the template. I can only assume
> that i selected this option on install but have since forgotten.
>
> CREATE DATABASE testing
> WITH OWNER = postgres
> ENCODING = 'UTF8'
> TABLESPACE = pg_default
> LC_COLLATE = 'English_United Kingdom.1252'
> LC_CTYPE = 'English_United Kingdom.1252'
> CONNECTION LIMIT = -1;
>
> Is there any way that i can change this, preferably against the template.
>
> If i try creating a new database by right clicking at the top of the
> database tree in pgAdmin i do note that i also have the options of "C" or
> "POSIX" but have read elsewhere that these are even more restrictive.
>
> Any ideas - hoping to avoid a complete re-install!

See:
http://www.postgresql.org/docs/9.0/interactive/multibyte.html#MULTIBYTE-CHARSET-
SUPPORTED

22.2.3. Automatic Character Set Conversion Between Server and Client

I am going to assume that the data load is the COPY you mentioned in the other
post. Before doing the COPY use one of the methods shown in the link above to
set the incoming encoding. This of course depends on you knowing what the
encoding is of the data you are importing:)

>
> Thanks in advance
>
> Andy
>
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com