Subject: Re: database encoding and collation.
To: pgsql-admin@lists.postgresql.org
References: 
 <PH0PR11MB5191DBEC457323D03B5C4592D6529@PH0PR11MB5191.namprd11.prod.outlook.com>
 <68331b01ee5ed0d02158747bf0e2d2e4476cbbb8.camel@cybertec.at>
 <PH0PR11MB5191393FFF0FDF0E510CF18CD6549@PH0PR11MB5191.namprd11.prod.outlook.com>
From: Ron <ronljohnsonjr@gmail.com>
Message-ID: <2ebc9c27-fd0e-2f8f-eee0-534277fb2e89@gmail.com>
Date: Thu, 13 Jan 2022 20:34:21 -0600
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.14.0
MIME-Version: 1.0
In-Reply-To: 
 <PH0PR11MB5191393FFF0FDF0E510CF18CD6549@PH0PR11MB5191.namprd11.prod.outlook.com>
Content-Type: multipart/alternative;
 boundary="------------502215ADD729E8EE80366BC3"
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/2ebc9c27-fd0e-2f8f-eee0-534277fb2e89%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------502215ADD729E8EE80366BC3
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

*Try* UTF-8 in a little test application, and *see what happens*.

On 1/13/22 8:11 PM, James Pang (chaolpan) wrote:
>      This question is about how to handle multiple languages in database , English, Japanese ,Chinese...  The Oracle database used WE8ISO8859P1 from long time ago , when put "Chinese" letters in database, client application just use  WE8ISO8859P1 (client encoding) to insert data into database , so no conversion there. When client app query the data , it still get the data with WE8ISO8859P1 , and then convert that data to UTF8 from client app side.   Now, we plan migrate to Postgresql 13,  want Postgresql database use WE8ISO8859P1 same as Oracle too, and same handling as Oracle.
>     Any potential risk ?
>
> Thanks,
>
> James
> -----Original Message-----
> From: Laurenz Albe <laurenz.albe@cybertec.at>
> Sent: Friday, January 14, 2022 3:20 AM
> To: James Pang (chaolpan) <chaolpan@cisco.com>; pgsql-admin@lists.postgresql.org
> Subject: Re: database encoding and collation.
>
> On Wed, 2022-01-12 at 13:31 +0000, James Pang (chaolpan) wrote:
>> We plan to migrate from Oracle to Postgresql 13, existing Oracle
>> database charaterset WE8ISO8859P1 , NLS_SORT = BINARY, NLS_COM=BINARY.
>> we want to keep same encoding and collation in Postgresql 13 as Oracle, so encoding=LATIN1 (Aliases ISO88591), what’s the suggested collation and ctype? Using ‘C’,’POSIX’, or en_US.LATIN1 ?
>> if using en_US.LATIN1 any impact to indexes and query where like % ?
>> The OS is Redhat 8, when export NLS_LANG=en_US.LATIN1, LC_COLLATE and
>> LC_CTYPE automatically set to en_US.LATIN1, and then initdb create database clusters with  collname, collation, ctype all = en_US.LATIN1.
> I advise against using any encoding other than UTF-8.
> What are you worried about?
>
> For the locale, you can get the same behavior as in Oracle by using "C" or "POSIX"
> (they are the same).
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>

-- 
Angular momentum makes the world go 'round.

--------------502215ADD729E8EE80366BC3
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <b>Try</b> UTF-8 in a little test application, and <b>see what
      happens</b>.<br>
    <br>
    <div class="moz-cite-prefix">On 1/13/22 8:11 PM, James Pang
      (chaolpan) wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:PH0PR11MB5191393FFF0FDF0E510CF18CD6549@PH0PR11MB5191.namprd11.prod.outlook.com">
      <pre class="moz-quote-pre" wrap="">    This question is about how to handle multiple languages in database , English, Japanese ,Chinese...  The Oracle database used WE8ISO8859P1 from long time ago , when put "Chinese" letters in database, client application just use  WE8ISO8859P1 (client encoding) to insert data into database , so no conversion there. When client app query the data , it still get the data with WE8ISO8859P1 , and then convert that data to UTF8 from client app side.   Now, we plan migrate to Postgresql 13,  want Postgresql database use WE8ISO8859P1 same as Oracle too, and same handling as Oracle. 
   Any potential risk ?

Thanks,

James
-----Original Message-----
From: Laurenz Albe <a class="moz-txt-link-rfc2396E" href="mailto:laurenz.albe@cybertec.at">&lt;laurenz.albe@cybertec.at&gt;</a> 
Sent: Friday, January 14, 2022 3:20 AM
To: James Pang (chaolpan) <a class="moz-txt-link-rfc2396E" href="mailto:chaolpan@cisco.com">&lt;chaolpan@cisco.com&gt;</a>; <a class="moz-txt-link-abbreviated" href="mailto:pgsql-admin@lists.postgresql.org">pgsql-admin@lists.postgresql.org</a>
Subject: Re: database encoding and collation.

On Wed, 2022-01-12 at 13:31 +0000, James Pang (chaolpan) wrote:
</pre>
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">We plan to migrate from Oracle to Postgresql 13, existing Oracle 
database charaterset WE8ISO8859P1 , NLS_SORT = BINARY, NLS_COM=BINARY. 
we want to keep same encoding and collation in Postgresql 13 as Oracle, so encoding=LATIN1 (Aliases ISO88591), what’s the suggested collation and ctype? Using ‘C’,’POSIX’, or en_US.LATIN1 ?
if using en_US.LATIN1 any impact to indexes and query where like % ?
The OS is Redhat 8, when export NLS_LANG=en_US.LATIN1, LC_COLLATE and 
LC_CTYPE automatically set to en_US.LATIN1, and then initdb create database clusters with  collname, collation, ctype all = en_US.LATIN1.
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
I advise against using any encoding other than UTF-8.
What are you worried about?

For the locale, you can get the same behavior as in Oracle by using "C" or "POSIX"
(they are the same).

Yours,
Laurenz Albe
--
Cybertec | <a class="moz-txt-link-freetext" href="https://www.cybertec-postgresql.com">https://www.cybertec-postgresql.com</a>

</pre>
    </blockquote>
    <br>
    <div class="moz-signature">-- <br>
      Angular momentum makes the world go 'round.</div>
  </body>
</html>

--------------502215ADD729E8EE80366BC3--