Re: cast to domain with default collation issue.

[jian he <jian.universality@xxxxxxxxx>]

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.johnston@xxxxxxxxx> wrote:

Please don’t top-post.

On Tuesday, May 24, 2022, jian he <jian.universality@xxxxxxxxx> 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