pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestion regarding UTF-8 data and "C" collation on definition of field of table
11+ messages / 6 participants
[nested] [flat]
* Question regarding UTF-8 data and "C" collation on definition of field of table
@ 2023-02-05 23:08 Dionisis Kontominas <dkontominas@gmail.com>
2023-02-05 23:24 ` Re: Question regarding UTF-8 data and "C" collation on definition of field of table Tom Lane <tgl@sss.pgh.pa.us>
2023-02-06 00:57 ` Re: Question regarding UTF-8 data and "C" collation on definition of field of table Ron <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 11+ messages in thread
From: Dionisis Kontominas @ 2023-02-05 23:08 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Hello all,
I have a question regarding the definition of the type of a character
field in a table and more specifically about its collation and UTF-8
characters and strings.
Let's say that the definition is for example as follows:
name character varying(8) COLLATE pg_catalog."C" NOT NULL
and also assume that the database default encoding is UTF8 and also the
Collate and Ctype is "C"". I plan to store strings of various languages in
this field.
Are these the correct settings that I should have used on creation of
the database?.
Thank you in Advance!
Kindest regards,
Dionisis Kontominas
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Question regarding UTF-8 data and "C" collation on definition of field of table
2023-02-05 23:08 Question regarding UTF-8 data and "C" collation on definition of field of table Dionisis Kontominas <dkontominas@gmail.com>
@ 2023-02-05 23:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-02-05 23:36 ` Re: Question regarding UTF-8 data and "C" collation on definition of field of table Dionisis Kontominas <dkontominas@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Tom Lane @ 2023-02-05 23:24 UTC (permalink / raw)
To: Dionisis Kontominas <dkontominas@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
Dionisis Kontominas <dkontominas@gmail.com> writes:
> Let's say that the definition is for example as follows:
> name character varying(8) COLLATE pg_catalog."C" NOT NULL
> and also assume that the database default encoding is UTF8 and also the
> Collate and Ctype is "C"". I plan to store strings of various languages in
> this field.
> Are these the correct settings that I should have used on creation of
> the database?.
Well, it won't crash or anything, but sorting will be according
to byte-by-byte values. So the sort order of non-ASCII text is
likely to look odd. How much do you care about that?
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Question regarding UTF-8 data and "C" collation on definition of field of table
2023-02-05 23:08 Question regarding UTF-8 data and "C" collation on definition of field of table Dionisis Kontominas <dkontominas@gmail.com>
2023-02-05 23:24 ` Re: Question regarding UTF-8 data and "C" collation on definition of field of table Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-02-05 23:36 ` Dionisis Kontominas <dkontominas@gmail.com>
2023-02-06 00:19 ` Re: Question regarding UTF-8 data and "C" collation on definition of field of table Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Dionisis Kontominas @ 2023-02-05 23:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@lists.postgresql.org
Hello Tom,
Thank you for your response.
I suppose that affects the outcome of ORDER BY clauses on the field,
along with the content of the indexes. Is this right?
Assuming that the requirement exists, to store UTF-8 characters on a
field that can be from multiple languages, and the database default
encoding is UTF8 which is the right thing I suppose (please verify), what
do you think should be the values of the Collation and Ctype for the
database to behave correctly? I could not find something specific in the
documentation.
What I did find interesting though is the below statement:
24.2.2.1. Standard Collations
"Additionally, the SQL standard collation name ucs_basic is available for
encoding UTF8. It is equivalent to C and sorts by Unicode code point."
Is this the right collation in the creation of the database in this use
case? If so, what would be the corresponding suitable Ctype?
Regards,
Dionisis
On Mon, 6 Feb 2023 at 00:24, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Dionisis Kontominas <dkontominas@gmail.com> writes:
> > Let's say that the definition is for example as follows:
> > name character varying(8) COLLATE pg_catalog."C" NOT NULL
> > and also assume that the database default encoding is UTF8 and also the
> > Collate and Ctype is "C"". I plan to store strings of various languages
> in
> > this field.
>
> > Are these the correct settings that I should have used on creation of
> > the database?.
>
> Well, it won't crash or anything, but sorting will be according
> to byte-by-byte values. So the sort order of non-ASCII text is
> likely to look odd. How much do you care about that?
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Question regarding UTF-8 data and "C" collation on definition of field of table
2023-02-05 23:08 Question regarding UTF-8 data and "C" collation on definition of field of table Dionisis Kontominas <dkontominas@gmail.com>
2023-02-05 23:24 ` Re: Question regarding UTF-8 data and "C" collation on definition of field of table Tom Lane <tgl@sss.pgh.pa.us>
2023-02-05 23:36 ` Re: Question regarding UTF-8 data and "C" collation on definition of field of table Dionisis Kontominas <dkontominas@gmail.com>
@ 2023-02-06 00:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-02-06 00:48 ` Re: Question regarding UTF-8 data and "C" collation on definition of field of table Dionisis Kontominas <dkontominas@gmail.com>
2023-02-06 01:14 ` Re: Question regarding UTF-8 data and "C" collation on definition of field of table Peter Geoghegan <pg@bowt.ie>
0 siblings, 2 replies; 11+ messages in thread
From: Tom Lane @ 2023-02-06 00:19 UTC (permalink / raw)
To: Dionisis Kontominas <dkontominas@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
Dionisis Kontominas <dkontominas@gmail.com> writes:
> I suppose that affects the outcome of ORDER BY clauses on the field,
> along with the content of the indexes. Is this right?
Yeah.
> Assuming that the requirement exists, to store UTF-8 characters on a
> field that can be from multiple languages, and the database default
> encoding is UTF8 which is the right thing I suppose (please verify), what
> do you think should be the values of the Collation and Ctype for the
> database to behave correctly?
Um ... so define "correct". If you have a mishmash of languages in the
same column, it's likely that they have conflicting rules about sorting,
and there may be no ordering that's not surprising to somebody.
If there's a predominant language in the data, selecting a collation
matching that seems like your best bet. Otherwise, maybe you should
just shrug your shoulders and stick with C collation. It's likely
to be faster than any alternative.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Question regarding UTF-8 data and "C" collation on definition of field of table
2023-02-05 23:08 Question regarding UTF-8 data and "C" collation on definition of field of table Dionisis Kontominas <dkontominas@gmail.com>
2023-02-05 23:24 ` Re: Question regarding UTF-8 data and "C" collation on definition of field of table Tom Lane <tgl@sss.pgh.pa.us>
2023-02-05 23:36 ` Re: Question regarding UTF-8 data and "C" collation on definition of field of table Dionisis Kontominas <dkontominas@gmail.com>
2023-02-06 00:19 ` Re: Question regarding UTF-8 data and "C" collation on definition of field of table Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-02-06 00:48 ` Dionisis Kontominas <dkontominas@gmail.com>
2023-02-06 01:30 ` Re: Question regarding UTF-8 data and "C" collation on definition of field of table Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 11+ messages in thread
From: Dionisis Kontominas @ 2023-02-06 00:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@lists.postgresql.org
Hi Tom,
1. Regarding the different languages in the same column, that is normal
if the column is a UTF-8 one, i.e. should be able to hold for example
English, Greek and Chinese characters. In this case what is the best
approach to define the collation and lctype of the column? Either C
or ucs_basic maybe or something else or it does not matter, the characters
would be stored correctly as long as the database is UTF8 encoding?
2. Is there a real difference between C and ucs_basic collation/lctype?
I have looked at the documentation and on the internet and most information
converges that they are actually the same as behavior is concerned;
actually no difference. What I suspect though as a non-similar is that C
after the ASCII part sorts the characters according to byte code point,
whereas the ucs_basic sorts those characters according to the Unicode code
point which may be different I suppose. Can you confirm?
3. In case the previous is correct I think that the most successful
collation for unicode columns would e to set the collation to
ucs_basic collation type and lctype accordingly if exists.
Regards,
Dionisis
On Mon, 6 Feb 2023 at 01:19, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Dionisis Kontominas <dkontominas@gmail.com> writes:
> > I suppose that affects the outcome of ORDER BY clauses on the field,
> > along with the content of the indexes. Is this right?
>
> Yeah.
>
> > Assuming that the requirement exists, to store UTF-8 characters on a
> > field that can be from multiple languages, and the database default
> > encoding is UTF8 which is the right thing I suppose (please verify), what
> > do you think should be the values of the Collation and Ctype for the
> > database to behave correctly?
>
> Um ... so define "correct". If you have a mishmash of languages in the
> same column, it's likely that they have conflicting rules about sorting,
> and there may be no ordering that's not surprising to somebody.
>
> If there's a predominant language in the data, selecting a collation
> matching that seems like your best bet. Otherwise, maybe you should
> just shrug your shoulders and stick with C collation. It's likely
> to be faster than any alternative.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Question regarding UTF-8 data and "C" collation on definition of field of table
2023-02-05 23:08 Question regarding UTF-8 data and "C" collation on definition of field of table Dionisis Kontominas <dkontominas@gmail.com>
2023-02-05 23:24 ` Re: Question regarding UTF-8 data and "C" collation on definition of field of table Tom Lane <tgl@sss.pgh.pa.us>
2023-02-05 23:36 ` Re: Question regarding UTF-8 data and "C" collation on definition of field of table Dionisis Kontominas <dkontominas@gmail.com>
2023-02-06 00:19 ` Re: Question regarding UTF-8 data and "C" collation on definition of field of table Tom Lane <tgl@sss.pgh.pa.us>
2023-02-06 00:48 ` Re: Question regarding UTF-8 data and "C" collation on definition of field of table Dionisis Kontominas <dkontominas@gmail.com>
@ 2023-02-06 01:30 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 11+ messages in thread
From: Tom Lane @ 2023-02-06 01:30 UTC (permalink / raw)
To: Dionisis Kontominas <dkontominas@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
Dionisis Kontominas <dkontominas@gmail.com> writes:
> 1. Regarding the different languages in the same column, that is normal
> if the column is a UTF-8 one, i.e. should be able to hold for example
> English, Greek and Chinese characters. In this case what is the best
> approach to define the collation and lctype of the column? Either C
> or ucs_basic maybe or something else or it does not matter, the characters
> would be stored correctly as long as the database is UTF8 encoding?
The characters will be stored correctly, yes. Collation only affects
sort order. Ctype affects some other functions like upper/lowercase
folding and what is considered a "letter" in regexps. If you use "C"
for lc_ctype then only ASCII letters will be folded or recognized as
letters.
> 2. Is there a real difference between C and ucs_basic collation/lctype?
No, at least not in Postgres.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Question regarding UTF-8 data and "C" collation on definition of field of table
2023-02-05 23:08 Question regarding UTF-8 data and "C" collation on definition of field of table Dionisis Kontominas <dkontominas@gmail.com>
2023-02-05 23:24 ` Re: Question regarding UTF-8 data and "C" collation on definition of field of table Tom Lane <tgl@sss.pgh.pa.us>
2023-02-05 23:36 ` Re: Question regarding UTF-8 data and "C" collation on definition of field of table Dionisis Kontominas <dkontominas@gmail.com>
2023-02-06 00:19 ` Re: Question regarding UTF-8 data and "C" collation on definition of field of table Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-02-06 01:14 ` Peter Geoghegan <pg@bowt.ie>
2023-02-09 13:49 ` Re: Question regarding UTF-8 data and "C" collation on definition of field of table Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
1 sibling, 1 reply; 11+ messages in thread
From: Peter Geoghegan @ 2023-02-06 01:14 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Dionisis Kontominas <dkontominas@gmail.com>; pgsql-general@lists.postgresql.org
On Sun, Feb 5, 2023 at 4:19 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> If there's a predominant language in the data, selecting a collation
> matching that seems like your best bet. Otherwise, maybe you should
> just shrug your shoulders and stick with C collation. It's likely
> to be faster than any alternative.
FWIW there are certain "compromise locales" supported by ICU/CLDR.
These include "English (Europe)", and, most notably, EOR (European
Ordering Rules):
https://en.wikipedia.org/wiki/European_ordering_rules
I'm not sure how widely used those are. EOR seems to have been
standardized by the EU or by an adjacent institution, so not sure how
widely used it really is.
It's also possible to use a custom collation with ICU, which is almost
infinitely flexible:
http://www.unicode.org/reports/tr10/#Customization
As an example, the rules about the relative ordering of each script
can be changed this way. There is also something called merged
tailorings.
The OP should see the Postgres ICU docs for hints on how to use these
facilities to make a custom collation that matches whatever their
requirements are:
https://www.postgresql.org/docs/current/collation.html#COLLATION-MANAGING
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Question regarding UTF-8 data and "C" collation on definition of field of table
2023-02-05 23:08 Question regarding UTF-8 data and "C" collation on definition of field of table Dionisis Kontominas <dkontominas@gmail.com>
2023-02-05 23:24 ` Re: Question regarding UTF-8 data and "C" collation on definition of field of table Tom Lane <tgl@sss.pgh.pa.us>
2023-02-05 23:36 ` Re: Question regarding UTF-8 data and "C" collation on definition of field of table Dionisis Kontominas <dkontominas@gmail.com>
2023-02-06 00:19 ` Re: Question regarding UTF-8 data and "C" collation on definition of field of table Tom Lane <tgl@sss.pgh.pa.us>
2023-02-06 01:14 ` Re: Question regarding UTF-8 data and "C" collation on definition of field of table Peter Geoghegan <pg@bowt.ie>
@ 2023-02-09 13:49 ` Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
0 siblings, 0 replies; 11+ messages in thread
From: Jehan-Guillaume de Rorthais @ 2023-02-09 13:49 UTC (permalink / raw)
To: Peter Geoghegan <pg@bowt.ie>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Dionisis Kontominas <dkontominas@gmail.com>; pgsql-general@lists.postgresql.org
On Sun, 5 Feb 2023 17:14:44 -0800
Peter Geoghegan <pg@bowt.ie> wrote:
...
> The OP should see the Postgres ICU docs for hints on how to use these
> facilities to make a custom collation that matches whatever their
> requirements are:
>
> https://www.postgresql.org/docs/current/collation.html#COLLATION-MANAGING
As you are talking about ICU customization to match whatever the requirement we
want, we were wondering if this would be that easy with ICU to build/create such
custom and odd collation (and not just move numbers after latin)? Even
being able to order letter by letter?
For the record, I helped on an issue last week to sort data using the ebcdic
order. ICU was just a pain, especially with this buggy and annoying bug when
sorting numbers after letters[1] and the fact that it takes whatever we feed it
with without complaining for badly formed or impossible collation. We just gave
up.
One of our recommendation was to write a glibc collation file, built/installed
it using localedef and "import" it in PostgreSQL using
pg_import_system_collations(). The customer actually did it and it works like a
charm.
Regards,
[1] remember ?
https://www.postgresql.org/message-id/flat/20200903105727.064665ce%40firost#0a6e89e58eec7679391c8292...
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Question regarding UTF-8 data and "C" collation on definition of field of table
2023-02-05 23:08 Question regarding UTF-8 data and "C" collation on definition of field of table Dionisis Kontominas <dkontominas@gmail.com>
@ 2023-02-06 00:57 ` Ron <ronljohnsonjr@gmail.com>
2023-02-06 01:07 ` Re: Question regarding UTF-8 data and "C" collation on definition of field of table Dionisis Kontominas <dkontominas@gmail.com>
2023-02-06 19:04 ` Re: Question regarding UTF-8 data and "C" collation on definition of field of table Peter J. Holzer <hjp-pgsql@hjp.at>
1 sibling, 2 replies; 11+ messages in thread
From: Ron @ 2023-02-06 00:57 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Why are you specifying the collation to be "C" when the default db encoding
is UTF8, and UTF-8 has Greek, Chinese and English encodings?
On 2/5/23 17:08, Dionisis Kontominas wrote:
> Hello all,
>
> I have a question regarding the definition of the type of a character
> field in a table and more specifically about its collation and UTF-8
> characters and strings.
>
> Let's say that the definition is for example as follows:
>
> name character varying(8) COLLATE pg_catalog."C" NOT NULL
>
> and also assume that the database default encoding is UTF8 and also the
> Collate and Ctype is "C"". I plan to store strings of various languages in
> this field.
>
> Are these the correct settings that I should have used on creation of
> the database?.
>
> Thank you in Advance!
>
> Kindest regards,
>
> Dionisis Kontominas
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Question regarding UTF-8 data and "C" collation on definition of field of table
2023-02-05 23:08 Question regarding UTF-8 data and "C" collation on definition of field of table Dionisis Kontominas <dkontominas@gmail.com>
2023-02-06 00:57 ` Re: Question regarding UTF-8 data and "C" collation on definition of field of table Ron <ronljohnsonjr@gmail.com>
@ 2023-02-06 01:07 ` Dionisis Kontominas <dkontominas@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Dionisis Kontominas @ 2023-02-06 01:07 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
Because if I don't specify the collation/lctype it seems to get the default
from the OS, which in my case is : English_Netherlands.1252 (database
encoding UTF8). That might not be best for truly unicode content columns,
so I investigated the "C" option, which also seems not to work; might be
worse.
To reframe my question, when you expect multilingual data in a column and
the database encoding is utf8, which seems to accommodate the need for
storage, what could be considered as best practice (if it can exist really)
for collation and lctype?
On Mon, 6 Feb 2023 at 01:57, Ron <ronljohnsonjr@gmail.com> wrote:
> Why are you specifying the collation to be "C" when the default db
> encoding
> is UTF8, and UTF-8 has Greek, Chinese and English encodings?
>
> On 2/5/23 17:08, Dionisis Kontominas wrote:
> > Hello all,
> >
> > I have a question regarding the definition of the type of a character
> > field in a table and more specifically about its collation and UTF-8
> > characters and strings.
> >
> > Let's say that the definition is for example as follows:
> >
> > name character varying(8) COLLATE pg_catalog."C" NOT NULL
> >
> > and also assume that the database default encoding is UTF8 and also the
> > Collate and Ctype is "C"". I plan to store strings of various languages
> in
> > this field.
> >
> > Are these the correct settings that I should have used on creation of
> > the database?.
> >
> > Thank you in Advance!
> >
> > Kindest regards,
> >
> > Dionisis Kontominas
>
> --
> Born in Arizona, moved to Babylonia.
>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Question regarding UTF-8 data and "C" collation on definition of field of table
2023-02-05 23:08 Question regarding UTF-8 data and "C" collation on definition of field of table Dionisis Kontominas <dkontominas@gmail.com>
2023-02-06 00:57 ` Re: Question regarding UTF-8 data and "C" collation on definition of field of table Ron <ronljohnsonjr@gmail.com>
@ 2023-02-06 19:04 ` Peter J. Holzer <hjp-pgsql@hjp.at>
1 sibling, 0 replies; 11+ messages in thread
From: Peter J. Holzer @ 2023-02-06 19:04 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 2023-02-05 18:57:13 -0600, Ron wrote:
> Why are you specifying the collation to be "C" when the default db encoding
> is UTF8, and UTF-8 has Greek, Chinese and English encodings?
C is equally bad for Greek, Chinese and English ;-)
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2023-02-09 13:49 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-02-05 23:08 Question regarding UTF-8 data and "C" collation on definition of field of table Dionisis Kontominas <dkontominas@gmail.com>
2023-02-05 23:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-02-05 23:36 ` Dionisis Kontominas <dkontominas@gmail.com>
2023-02-06 00:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-02-06 00:48 ` Dionisis Kontominas <dkontominas@gmail.com>
2023-02-06 01:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-02-06 01:14 ` Peter Geoghegan <pg@bowt.ie>
2023-02-09 13:49 ` Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
2023-02-06 00:57 ` Ron <ronljohnsonjr@gmail.com>
2023-02-06 01:07 ` Dionisis Kontominas <dkontominas@gmail.com>
2023-02-06 19:04 ` Peter J. Holzer <hjp-pgsql@hjp.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