pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDatabase encoding and collation
9+ messages / 5 participants
[nested] [flat]
* Database encoding and collation
@ 2013-04-20 00:12 Rodrigo Barboza <rodrigombufrj@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Rodrigo Barboza @ 2013-04-20 00:12 UTC (permalink / raw)
To: pgsql-admin
Hi guys.
I created a database with default encoding (SQL_ASCII) and default collate
(C).
I created a table test like this:
create table test (a varchar (10));
Then i executed "insert into teste (a) values ('áéç&ã','Æ','ß');
After that:
select * from test;
a
---------
áéç&ã
Æ
ß
Why did it stora correctly if those values are not ASCII?
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Database encoding and collation
@ 2013-04-20 16:52 Craig James <cjames@emolecules.com>
parent: Rodrigo Barboza <rodrigombufrj@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Craig James @ 2013-04-20 16:52 UTC (permalink / raw)
To: Rodrigo Barboza <rodrigombufrj@gmail.com>; +Cc: pgsql-admin
On Fri, Apr 19, 2013 at 5:12 PM, Rodrigo Barboza <rodrigombufrj@gmail.com>wrote:
> Hi guys.
> I created a database with default encoding (SQL_ASCII) and default collate
> (C).
> I created a table test like this:
> create table test (a varchar (10));
> Then i executed "insert into teste (a) values ('áéç&ã','Æ','ß');
>
> After that:
> select * from test;
> a
> ---------
> áéç&ã
> Æ
> ß
>
> Why did it stora correctly if those values are not ASCII?
>
Characters are interpreted and displayed by your terminal, not the Postgres
system. I suspect that you have language settings on whatever windowing
system you use. Postgres merely interprets the characters you send as a
series of 8-bit bytes. It's up to your display system to interpret them.
If you change your display terminal's language, I expect you'll see
something different.
The language settings of Postgres don't change what is stored, only how it
is interpreted (such as sorting).
Craig
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Database encoding and collation
@ 2013-04-20 17:03 Rodrigo Barboza <rodrigombufrj@gmail.com>
parent: Craig James <cjames@emolecules.com>
0 siblings, 0 replies; 9+ messages in thread
From: Rodrigo Barboza @ 2013-04-20 17:03 UTC (permalink / raw)
To: Craig James <cjames@emolecules.com>; +Cc: pgsql-admin
On Sat, Apr 20, 2013 at 1:52 PM, Craig James <cjames@emolecules.com> wrote:
>
> On Fri, Apr 19, 2013 at 5:12 PM, Rodrigo Barboza <rodrigombufrj@gmail.com>wrote:
>
>> Hi guys.
>> I created a database with default encoding (SQL_ASCII) and default
>> collate (C).
>> I created a table test like this:
>> create table test (a varchar (10));
>> Then i executed "insert into teste (a) values ('áéç&ã','Æ','ß');
>>
>> After that:
>> select * from test;
>> a
>> ---------
>> áéç&ã
>> Æ
>> ß
>>
>> Why did it stora correctly if those values are not ASCII?
>>
>
> Characters are interpreted and displayed by your terminal, not the
> Postgres system. I suspect that you have language settings on whatever
> windowing system you use. Postgres merely interprets the characters you
> send as a series of 8-bit bytes. It's up to your display system to
> interpret them. If you change your display terminal's language, I expect
> you'll see something different.
>
> The language settings of Postgres don't change what is stored, only how it
> is interpreted (such as sorting).
>
> Craig
>
>
I see.
When you say "Postgres merely interprets the characters you send as a
series of 8-bit bytes", you meant for SQL_ASCII or every encoding?
Isn't sorting defined by collation?
Could I dump my database, create a new one with LATIN1 and restores?
^ permalink raw reply [nested|flat] 9+ messages in thread
* database encoding and collation.
@ 2022-01-12 13:31 James Pang (chaolpan) <chaolpan@cisco.com>
0 siblings, 1 reply; 9+ messages in thread
From: James Pang (chaolpan) @ 2022-01-12 13:31 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
Thanks,
James
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: database encoding and collation.
@ 2022-01-13 19:19 Laurenz Albe <laurenz.albe@cybertec.at>
parent: James Pang (chaolpan) <chaolpan@cisco.com>
0 siblings, 1 reply; 9+ messages in thread
From: Laurenz Albe @ 2022-01-13 19:19 UTC (permalink / raw)
To: James Pang (chaolpan) <chaolpan@cisco.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: database encoding and collation.
@ 2022-01-14 02:11 James Pang (chaolpan) <chaolpan@cisco.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 3 replies; 9+ messages in thread
From: James Pang (chaolpan) @ 2022-01-14 02:11 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: database encoding and collation.
@ 2022-01-14 02:14 James Pang (chaolpan) <chaolpan@cisco.com>
parent: James Pang (chaolpan) <chaolpan@cisco.com>
2 siblings, 0 replies; 9+ messages in thread
From: James Pang (chaolpan) @ 2022-01-14 02:14 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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 ?
When using locale "C" or "POSIX", only A-Z characters got sorted by binary , for other languages like French or Chinese, any suggestions?
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: database encoding and collation.
@ 2022-01-14 02:34 Ron <ronljohnsonjr@gmail.com>
parent: James Pang (chaolpan) <chaolpan@cisco.com>
2 siblings, 0 replies; 9+ messages in thread
From: Ron @ 2022-01-14 02:34 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
*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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: database encoding and collation.
@ 2022-01-14 07:17 Laurenz Albe <laurenz.albe@cybertec.at>
parent: James Pang (chaolpan) <chaolpan@cisco.com>
2 siblings, 0 replies; 9+ messages in thread
From: Laurenz Albe @ 2022-01-14 07:17 UTC (permalink / raw)
To: James Pang (chaolpan) <chaolpan@cisco.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2022-01-14 07:17 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-04-20 00:12 Database encoding and collation Rodrigo Barboza <rodrigombufrj@gmail.com>
2013-04-20 16:52 ` Craig James <cjames@emolecules.com>
2013-04-20 17:03 ` Rodrigo Barboza <rodrigombufrj@gmail.com>
2022-01-12 13:31 database encoding and collation. James Pang (chaolpan) <chaolpan@cisco.com>
2022-01-13 19:19 ` Re: database encoding and collation. Laurenz Albe <laurenz.albe@cybertec.at>
2022-01-14 02:11 ` RE: database encoding and collation. James Pang (chaolpan) <chaolpan@cisco.com>
2022-01-14 02:14 ` RE: database encoding and collation. James Pang (chaolpan) <chaolpan@cisco.com>
2022-01-14 02:34 ` Re: database encoding and collation. Ron <ronljohnsonjr@gmail.com>
2022-01-14 07:17 ` Re: database encoding and collation. Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox