Thread: encoding and LC_COLLATE

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!

Thanks in advance

Andy


--
View this message in context: http://postgresql.1045698.n5.nabble.com/encoding-and-LC-COLLATE-tp4990415p4990415.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Hi,

In response to my own question i have now read the following:

http://stackoverflow.com/questions/6579621/lc-collate-and-lc-ctype-suport-for-utf-8-in-postgresql

It seems to show that apart from a complete reinstall i should just use a
Locale of C (as i'm on Windows and POSIX wont work).

This is a bit of an issue as i wanted to use a PostGIS template table with
all of the functions assigned but i can only create a new database with
Locale C by setting the template as template_0.

So, any info on being able to reset the value on an existing table would be
much appreciate - otherwise i'll get on to Postgres 9.1 and start reloading
all the data...

Thanks

Andy

--
View this message in context: http://postgresql.1045698.n5.nabble.com/encoding-and-LC-COLLATE-tp4990415p4990605.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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.klaver@gmail.com

De : pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] De la part de Adrian Klaver
>Envoyé : 14 novembre 2011 13:03
>...
>
>Second is the data coming in actually UTF8 or some other encoding?
>... 

Hi Andy,
I have to agree with Adrian in that the data may be coming in under a
different encoding. An e acute is a valid character in 1252 encoding.
However, if the source computer is using, for example, code page 850, an e
acute is hex(82) whereas the equivalent in 1252 is hex(e9). UTF-8 "doesn't
like" hex(82).
HTH,
Mark

Hi Adrian/Mark

Thanks again for your help, i have now got the load working by setting the
encoding to WIN1252. I had been assuming i was setting it to UTF8

SET CLIENT_ENCODING TO 'WIN1252';

Andy

--
View this message in context: http://postgresql.1045698.n5.nabble.com/encoding-and-LC-COLLATE-tp4990415p4994930.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.