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 |
+---+-----+
*/
