Thread: create a database with encoding LATIN1

create a database with encoding LATIN1

From
Didier Gasser-Morlay
Date:
This is how I do it: when you setup your db cluster, chose the locale 'C' this will let you select LATIN1 as a database encoding. (or as fas as I understand, any other encoding) 

If you let it at the default value, the cluster lc is defaulted to the serveur local charset, in your case UTF8 - English_Canada.1252 (as would any modern windows/linux server be nowadays) 

Hope this helps
Didier

Re: create a database with encoding LATIN1

From
Grzegorz Szpetkowski
Date:
2011/5/3 Didier Gasser-Morlay <didiergm@gmail.com>:
> This is how I do it: when you setup your db cluster, chose the locale 'C'
> this will let you select LATIN1 as a database encoding. (or as fas as I
> understand, any other encoding)
> If you let it at the default value, the cluster lc is defaulted to the
> serveur local charset, in your case UTF8 - English_Canada.1252 (as would any
> modern windows/linux server be nowadays)
> Hope this helps
> Didier

That's good point. You can use C/POSIX locale with LATIN1 or any other
encoding, but in such case you lost some (probably useful) text
functionalities, which are provided by specific locale (upper, lower,
and initcap functions bahaviour, sort order using ORDER BY etc.).

"For C or POSIX locale, any character set is allowed, but for other
locales there is only one character set that will work correctly. (On
Windows, however, UTF-8 encoding can be used with any locale.)"

"If you want the system to behave as if it had no locale support, use
the special locale C or POSIX."

"The drawback of using locales other than C or POSIX in PostgreSQL is
its performance impact. It slows character handling and prevents
ordinary indexes from being used by LIKE. For this reason use locales
only if you actually need them."

Regards,
Grzegorz Szpetkowski

Re: create a database with encoding LATIN1

From
Sébastien D'Errico
Date:
Thank you Didier and Grzegorz!

That clear some part.

Curiosity, am I right now in the from of this windows installation of
Postgres:
http://www.hollox.net/a/locale.png

I know that I need LATIN1 for the project. If I check this reference sheet:
http://www.postgresql.org/docs/8.4/static/multibyte.html

I get:
LATIN1    ISO 8859-1, ECMA 94    Western European    Yes    1    ISO88591

In the list from the printscreen, I do not see LATIN1, No ISO, No Western
European.

How can I know what I need to choose to get LATIN1?

Do I look the wrong setting?

Thanks,
Sebastien

-----Original Message-----
From: Grzegorz Szpetkowski [mailto:gszpetkowski@gmail.com]
Sent: May-03-11 11:19 AM
To: Didier Gasser-Morlay; sebastien@hollox.net
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] create a database with encoding LATIN1

2011/5/3 Didier Gasser-Morlay <didiergm@gmail.com>:
> This is how I do it: when you setup your db cluster, chose the locale 'C'
> this will let you select LATIN1 as a database encoding. (or as fas as I
> understand, any other encoding)
> If you let it at the default value, the cluster lc is defaulted to the
> serveur local charset, in your case UTF8 - English_Canada.1252 (as would any
> modern windows/linux server be nowadays)
> Hope this helps
> Didier

That's good point. You can use C/POSIX locale with LATIN1 or any other
encoding, but in such case you lost some (probably useful) text
functionalities, which are provided by specific locale (upper, lower,
and initcap functions bahaviour, sort order using ORDER BY etc.).

"For C or POSIX locale, any character set is allowed, but for other
locales there is only one character set that will work correctly. (On
Windows, however, UTF-8 encoding can be used with any locale.)"

"If you want the system to behave as if it had no locale support, use
the special locale C or POSIX."

"The drawback of using locales other than C or POSIX in PostgreSQL is
its performance impact. It slows character handling and prevents
ordinary indexes from being used by LIKE. For this reason use locales
only if you actually need them."

Regards,
Grzegorz Szpetkowski

Attachment

Re: create a database with encoding LATIN1

From
Grzegorz Szpetkowski
Date:
I see that you can only select "English, Canada" locale (and obviously
POSIX/C), which has implicitly WIN1252 encoding and you can't choose
any other variant locale from there.

However you can create new database with appropriate locale and LATIN1
encoding in such way:

CREATE DATABASE testdb
  WITH OWNER = postgres
  ENCODING = 'LATIN1'
  LC_COLLATE = 'eng_canada.28591'
  LC_CTYPE = 'eng_canada.28591'
  TEMPLATE = template0;

If you want get eng_canada.28591 encoding for whole database cluster,
then you need to drop existing one and then use initdb with -locale
param.

More information:
http://msdn.microsoft.com/en-us/library/hzz3tw78

Regards,
Grzegorz Szpetkowski

2011/5/3 Sébastien D'Errico <sebastien@hollox.net>:
> Thank you Didier and Grzegorz!
>
> That clear some part.
>
> Curiosity, am I right now in the from of this windows installation of
> Postgres:
> http://www.hollox.net/a/locale.png
>
> I know that I need LATIN1 for the project. If I check this reference sheet:
> http://www.postgresql.org/docs/8.4/static/multibyte.html
>
> I get:
> LATIN1  ISO 8859-1, ECMA 94     Western European        Yes     1       ISO88591
>
> In the list from the printscreen, I do not see LATIN1, No ISO, No Western
> European.
>
> How can I know what I need to choose to get LATIN1?
>
> Do I look the wrong setting?
>
> Thanks,
> Sebastien
>
> -----Original Message-----
> From: Grzegorz Szpetkowski [mailto:gszpetkowski@gmail.com]
> Sent: May-03-11 11:19 AM
> To: Didier Gasser-Morlay; sebastien@hollox.net
> Cc: pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] create a database with encoding LATIN1
>
> 2011/5/3 Didier Gasser-Morlay <didiergm@gmail.com>:
>> This is how I do it: when you setup your db cluster, chose the locale 'C'
>> this will let you select LATIN1 as a database encoding. (or as fas as I
>> understand, any other encoding)
>> If you let it at the default value, the cluster lc is defaulted to the
>> serveur local charset, in your case UTF8 - English_Canada.1252 (as would any
>> modern windows/linux server be nowadays)
>> Hope this helps
>> Didier
>
> That's good point. You can use C/POSIX locale with LATIN1 or any other
> encoding, but in such case you lost some (probably useful) text
> functionalities, which are provided by specific locale (upper, lower,
> and initcap functions bahaviour, sort order using ORDER BY etc.).
>
> "For C or POSIX locale, any character set is allowed, but for other
> locales there is only one character set that will work correctly. (On
> Windows, however, UTF-8 encoding can be used with any locale.)"
>
> "If you want the system to behave as if it had no locale support, use
> the special locale C or POSIX."
>
> "The drawback of using locales other than C or POSIX in PostgreSQL is
> its performance impact. It slows character handling and prevents
> ordinary indexes from being used by LIKE. For this reason use locales
> only if you actually need them."
>
> Regards,
> Grzegorz Szpetkowski
>

Re: create a database with encoding LATIN1

From
Grzegorz Szpetkowski
Date:
I also asked question at
http://forums.enterprisedb.com/posts/list/2747.page I am sure that I
saw installer with encoding selection option for locale e.g.
http://pginstaller.projects.postgresql.org/pginst5.png in which you
can specify database cluster encoding. Unfortunately AFAIS pgInstaller
is not available for PostgreSQL 8.2+.

Regards,
Grzegorz Szpetkowski

2011/5/3 Sébastien D'Errico <sebastien@hollox.net>:
> Thank you Didier and Grzegorz!
>
> That clear some part.
>
> Curiosity, am I right now in the from of this windows installation of
> Postgres:
> http://www.hollox.net/a/locale.png
>
> I know that I need LATIN1 for the project. If I check this reference sheet:
> http://www.postgresql.org/docs/8.4/static/multibyte.html
>
> I get:
> LATIN1  ISO 8859-1, ECMA 94     Western European        Yes     1       ISO88591
>
> In the list from the printscreen, I do not see LATIN1, No ISO, No Western
> European.
>
> How can I know what I need to choose to get LATIN1?
>
> Do I look the wrong setting?
>
> Thanks,
> Sebastien
>
> -----Original Message-----
> From: Grzegorz Szpetkowski [mailto:gszpetkowski@gmail.com]
> Sent: May-03-11 11:19 AM
> To: Didier Gasser-Morlay; sebastien@hollox.net
> Cc: pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] create a database with encoding LATIN1
>
> 2011/5/3 Didier Gasser-Morlay <didiergm@gmail.com>:
>> This is how I do it: when you setup your db cluster, chose the locale 'C'
>> this will let you select LATIN1 as a database encoding. (or as fas as I
>> understand, any other encoding)
>> If you let it at the default value, the cluster lc is defaulted to the
>> serveur local charset, in your case UTF8 - English_Canada.1252 (as would any
>> modern windows/linux server be nowadays)
>> Hope this helps
>> Didier
>
> That's good point. You can use C/POSIX locale with LATIN1 or any other
> encoding, but in such case you lost some (probably useful) text
> functionalities, which are provided by specific locale (upper, lower,
> and initcap functions bahaviour, sort order using ORDER BY etc.).
>
> "For C or POSIX locale, any character set is allowed, but for other
> locales there is only one character set that will work correctly. (On
> Windows, however, UTF-8 encoding can be used with any locale.)"
>
> "If you want the system to behave as if it had no locale support, use
> the special locale C or POSIX."
>
> "The drawback of using locales other than C or POSIX in PostgreSQL is
> its performance impact. It slows character handling and prevents
> ordinary indexes from being used by LIKE. For this reason use locales
> only if you actually need them."
>
> Regards,
> Grzegorz Szpetkowski
>