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.
Please don’t top-post.
On Tuesday, May 24, 2022, jian he <jian.universality@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 text) 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