Re: database encoding and collation.

On Fri, 2022-01-14 at 02:11 +0000, 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 ?

I see the problem: the sloppy checking of Oracle allowed you to insert corrupted strings into
the Oracle database by setting client encoding equal to server encoding, and you might not
detect the problem until you try to read the data with a different client encoding.

You may be able to carry on like this in PostgreSQL, since all bytes are valid LATIN1
code points, but (like in Oracle) you will run into problems as soon as you access the
database with a different client encoding, for example with a JDBC driver.

It would be better to fix the problem. If all the data in the database are actually
encoded in a certain encoding (different from ISO-8859-1), you could simply import them
into a database with that encoding, and you would have the benefit of having correct data.
If different strings are encoded in different encodings, then you would have to repair the
data before doing that.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com