cast to domain with default collation issue.

CREATE DOMAIN testdomain AS text;

 --asume the default collation is as per show LC_COLLATE;

– on my pc, it is C.UTF-8.

--So the testdomain will be collation "C.UTF-8"
------------

=> \d collate_test1

            Table "test.collate_test1"

 Column |  Type   | Collation | Nullable | Default

--------+---------+-----------+----------+---------

 a      | integer |           |          |

 b      | text    | en-x-icu  | not null |

=> \d collate_test2

            Table "test.collate_test2"

 Column |  Type   | Collation | Nullable | Default

--------+---------+-----------+----------+---------

 a      | integer |           |          |

 b      | text    | sv-x-icu  |          |

=> \d collate_test3

            Table "test.collate_test3"

 Column |  Type   | Collation | Nullable | Default

--------+---------+-----------+----------+---------

 a      | integer |           |          |
 b      | text    | C         |          |
-----------------------------------------------

My guess is that the following should be the same. Since the same content
in the end will be cast to the same collation. However the following output
contradicts with my understanding.


SELECT a, b::testdomain FROM collate_test1 ORDER BY 2;

/*

 +---+-----+

| a |  b  |

+---+-----+

| 1 | abc |

| 4 | ABC |

| 2 | äbc |

| 3 | bbc |

+---+-----+

 */

SELECT a, b::testdomain FROM collate_test2 ORDER BY 2;

/*

 +---+-----+

| a |  b  |

+---+-----+

| 1 | abc |

| 4 | ABC |

| 3 | bbc |

| 2 | äbc |

+---+-----+

 */

SELECT a, b::testdomain FROM collate_test3 ORDER BY 2;

/*

 +---+-----+

| a |  b  |

+---+-----+

| 4 | ABC |

| 1 | abc |

| 3 | bbc |

| 2 | äbc |

+---+-----+

 */


-- 
 I recommend David Deutsch's <<The Beginning of Infinity>>

  Jian