postgresql 15 manual parts:
Otherwise, all input expressions must have the same implicit collation
> derivation or the default collation. If any non-default collation is
> present, that is the result of the collation combination. Otherwise, the
> result is the default collation.
>
For example, consider this table definition:
>
> CREATE TABLE test1 (
> a text COLLATE "de_DE",
> b text COLLATE "es_ES",
> ...
> );
>
> Then in
>
> SELECT a < 'foo' FROM test1;
>
> the < comparison is performed according to de_DE rules, because the
> expression combines an implicitly derived collation with the default
> collation.
>
query: * SELECT a < 'foo' FROM test1;*
is an example of {{If any non-default collation is present, that is the
result of the collation combination. }}
So it should be something like {{ if any side of expression don't have
implicit derived collation is present, that is the result of the collation
combination}
I personally feel wording *non-default* may not be that correct. Because if
the column is text then it automatically at least has default collation.
see manual quote about default collation:
> The collation of an expression can be the “default” collation, which
> means the locale settings defined for the database. It is also possible for
> an expression's collation to be indeterminate. In such cases, ordering
> operations and other operations that need to know the collation will fail.
>
On Wed, May 25, 2022 at 12:08 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> Please don’t top-post.
>
> On Tuesday, May 24, 2022, jian he <jian.universal...@gmail.com> wrote:
>
>>
>> Otherwise, all input expressions must have the same implicit collation
>>> derivation or the default collation. If any non-default collation is
>>> present, that is the result of the collation combination. Otherwise, the
>>> result is the default collation.
>>>
>>
>> I think the above quote part can be used to explain the following
>> examples.
>>
>>> CREATE TABLE test1 ( a text COLLATE "de_DE", b text COLLATE "es_ES", c
>>> text );
>>> SELECT a < 'foo' FROM test1;
>>
>> SELECT c < 'foo' FROM test1;
>>
>> But the *non-default* seems not that correct for me. Like a column if it
>> does not mention anything, then the default value is null. So
>> * create table test111( a tex*t) The default collation for column a is the
>> same as the output of *show lc_collate*.
>>
>> so there is no *non-default? *
>>
>>
> I’m not following the point you are trying to make. table111.a
> contributes the default collation for any expression needing a collation
> implicitly resolved.
>
> David J.
>
>
--
I recommend David Deutsch's <<The Beginning of Infinity>>
Jian
