1

In PostgreSQL, what is the difference between collations C and C.UTF-8?

Both show up in rows of pg_collation. Is it perhaps the case that C.UTF-8 is the same as C with encoding UTF-8 regardless or what the actual encoding of a database is?

2

Is it perhaps the case that C.UTF-8 is the same as C with encoding UTF-8

No. Consider for instance these differences in an UTF-8 database, on Debian Linux:

postgres=# select upper('é' collate "C"), upper('é' collate "C.UTF-8");
 upper | upper 
-------+-------
 é     | É
(1 row)

postgres=# select ('A' < E'\u0378' collate "C"),
                  ('A' < E'\u0378' collate "C.UTF-8");
 ?column? | ?column? 
----------+----------
 t        | f
(1 row)

(U+0378 does not correspond to any valid character in Unicode).

In short, C does not reason about anything that is beyond U+007F, while C.UTF-8 does.

But note that the behavior and even the existence of C.UTF-8 depends on the particular libc (the system's C library) of your operating system. In that sense, the question of what precisely PostgreSQL does for such or such locale is bogus, because it's libc that does these things, not PostgreSQL proper.

That's true at least for libc-backed collations. Starting with Postgres version 10, ICU collations may be used. These collations are consistent across operating systems.

Until proven otherwise, the "C" locale also provides consistent results across operating systems, but I think it's the only one among those provided by libc, considering all operating systems.

  • So C and C.UTF-8 are not the same if non-Unicode characters come into play. How about apart from this corner case (and still assuming that the database uses UTF-8 encoding)? – rookie099 Jun 21 at 7:50
  • The potentially incorrect case conversions of all characters above U+007F is not just a corner case. – Daniel Vérité Jun 21 at 9:19
  • This valid Unicode character above U+007F apparently does not raise the issue: SELECT ('A' < E'\u0153' COLLATE "C"), ('A' < E'\u0153' COLLATE "C.UTF-8");. Is your argument that this concerns non-Unicode characters above U+007F? – rookie099 Jun 21 at 10:24
  • Hi Daniel (and @rookie099 ): Thanks for pointing out these two differences (+1). I am a little unclear, though, on what is meant by "C does not reason about anything that is beyond U+007F, while C.UTF-8 does". Yes, C.UTF-8 does recognize that U+0378 is undefined, but that just sorts it at the top / beginning. It also sorts that code point ahead of both U+0379 (undefined) and U+0020 (space), but can't sort é ahead of Ó. Please see my extended tests here: dbfiddle.uk/… – Solomon Rutzky Jun 21 at 19:28
  • All I'm saying is that C.UTF-8 is not an alias of "C" given an UTF-8 encoding, because I've found behavioral differences. I cannot comment on whether these differences are important for your use cases. Also please keep in mind you're commenting GNU libc here, not PostgreSQL. PG does run on FreeBSD or Windows and C.UTF-8 doesn't even exist on these systems as a collation. – Daniel Vérité Jun 22 at 15:48
2

The PostgreSQL documentation leaves a lot to be desired (just sayin' 😼 ).

To start with, there is only one encoding for a particular database, so C and C.UTF-8 in your UTF-8 database are both using the UTF-8 encoding.

For libc collations: typically collation names, by convention, are truly two-part names of the following structure:

{locale_name}.{encoding_name}

A "locale" (i.e. "culture") is the set of language-specific rules for sorting (LC_COLLATE) and capitalization (LC_CTYPE). Even though there is sometimes overlap, this really doesn't have anything to do with how this data is stored.

An "encoding" is how the data is stored (i.e. what byte sequence equates to which character). Even though there is sometimes overlap, this really doesn't have anything to do with the sorting and capitalization rules of any particular language that uses the encoding (some encodings can be used by multiple languages that can have quite different rules in one or both of those areas).

To illustrate, consider storing Korean data:

  • ko_KR is the locale.
  • Possible encodings that work with this locale are:
    • EUC_KR (Extended UNIX Code-KR)
    • JOHAB
    • UHC (Unified Hangul Code / Windows949)
    • UTF8 (Unicode's 8-bit encoding)

Also consider the following, taken from the "Collation Support: libc collations" documentation (emphasis added):

For example, the operating system might provide a locale named de_DE.utf8. initdb would then create a collation named de_DE.utf8 for encoding UTF8 ... It will also create a collation with the .utf8 tag stripped off the name. So you could also use the collation under the name de_DE, which is less cumbersome to write and makes the name less encoding-dependent...

...

Within any particular database, only collations that use that database's encoding are of interest. Other entries in pg_collation are ignored. Thus, a stripped collation name such as de_DE can be considered unique within a given database even though it would not be unique globally. Use of the stripped collation names is recommended, since it will make one less thing you need to change if you decide to change to another database encoding. Note however that the default, C, and POSIX collations can be used regardless of the database encoding.

Meaning, in a database that uses the UTF-8 encoding, en_US and en_US.UTF8 are equivalent. BUT, between that database and a database that uses the LATIN1 encoding, the en_US collations are not equivalent.

So, does this mean that C and C.UTF-8 are the same?

NO, that would be too easy!!! The C collation is an exception to the above-stated behavior. The C collation is a simple set of rules that is available regardless of the database's encoding, and the behavior should be consistent across encodings (which is made possible by only recognizing the US English alphabet — "a-z" and "A-Z" — as "letters", and sorting by byte value, which should be the same for the encodings available to you).

The C.UTF-8 collation is actually a slightly enhanced set of rules, as compared to the base C rules. This difference can actually be seen in pg_collation since the values for the collcollate and collctype columns are different between the rows for C and C.UTF-8.

I put together a set of test queries to illustrate some of the similarities and differences between these two collations, as well as compared to en_GB (and implicitly en_GB.utf8). I started with the queries provided in Daniel Vérité's answer, enhanced them to hopefully be clearer about what is and is not being shown, and added a few queries. The results show us that:

  1. C and C.UTF-8 are actually different sets of rules, even if only slightly different, based on their respective values in the collcollate and collctype columns in pg_collation (final query)
  2. C.UTF-8 expands the characters that are considered "letters"
  3. C.UTF-8, unlike C (but like en_GB), recognizes invalid Unicode code points (i.e. U+0378) and sorts them towards the top
  4. C.UTF-8, like C (but unlike en_GB), sorts non-US-English-letter characters by code point
  5. ucs_basic appears to be equivalent to C (which is stated in the documentation)

You can find, and execute, the queries on: db<>fiddle

  • +1 esp. for "Use of the stripped collation names is recommended". – rookie099 Jun 21 at 7:51
  • I still find this hard to square with @DanielVérité examples. Perhaps C and C.UTF-8 are equivalent collations (as in pg_collation) in terms of string sort order (LC_COLLATE; when the database uses UTF-8 encoding and if there are no invalid Unicode characters) but not in terms of character classification (LC_CTYPE). C treats only ASCII letters “A” through “Z” as letters, perhaps C.UTF-8 also treats é as a letter? – rookie099 Jun 21 at 11:58
  • 1
    @rookie099 I did a bit of testing and reworked my answer, especially in light of the points made by Daniel Vérité. – Solomon Rutzky Jun 21 at 18:30
1

From the postgresql documentation, https://www.postgresql.org/docs/11/collation.html:

23.2.2.1. Standard Collations

On all platforms, the collations named default, C, and POSIX are available. Additional collations may be available depending on operating system support. The default collation selects the LC_COLLATE and LC_CTYPE values specified at database creation time. The C and POSIX collations both specify “traditional C” behavior, in which only the ASCII letters “A” through “Z” are treated as letters, and sorting is done strictly by character code byte values.

Additionally, the SQL standard collation name ucs_basic is available for encoding UTF8. It is equivalent to C and sorts by Unicode code point.

So, if my understanding is correct, C is ASCII, not UTF8.

  • Yes, I (currently) also think it's ASCII in terms of character classification (LC_CTYPE). See other comment. – rookie099 Jun 21 at 12:37
  • 1
    Hi @rookie099 and Michael: No, this answer is confusing encoding with collation and/or classification. The encoding in this database is UTF-8 for all collations. What they are saying about C is that only the US English letters, found in the standard ASCII set -- values 0 to 127 -- will sort as letters (i.e. "A" with "a", "B" with "b", etc) and can uppercase / lowercase. All other characters, whether or not they are ASCII, Extended ASCII, or Unicode-only, will sort according to their byte value and cannot uppercase/lowercase. – Solomon Rutzky Jun 21 at 19:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.