I added one column with generated always column with UPPER CASE like below:-
Alter table table_name t add column data varchar(8000) generated always as (UPPER(t.content)) stored
Data column is generated always constraint here
This column has many sentences for each row in which some of the characters are in Greek language.
Like µ, ë, ä, Ä etc..
So, for the example testµ when I choose
1. Select UPPER('testµ')
Output :- TESTM
But as per mail conversation I have used COLLATE ucs_basic like
2. Select UPPER('testµ' collate "ucs_basic")
Output :- TESTµ (which is correct)
Output :- MASS (which is correct)
4. Select data from table (here data is the column which is created with generated always column like mentioned above)
For some of the rows which contains Greek characters I'm getting wrong output.
For ex:- for the word 'MASS' I'm getting 'µASS' when I select the data from the table
Summary:- I'm getting wrong output when I use upper keyword with collation for the table
But when I explicitly call upper keyword with collation like mentioned in above I'm getting the results as expected.
Even I tried to add collation in the column itself but it didn't worked.
Alter table table_name t add column data varchar(8000) generated always as (UPPER(t.content, collation "ucs_basic")) stored
Or
Alter table table_name t add column data varchar(8000) generated always as (UPPER(t.content) collation "ucs_basic") stored
Both didn't worked. As I got wrong output when I selected the data from the table.
On Wed, 6 Sep, 2023, 10:18 pm Erik Wienhold, <ewie@xxxxxxxxx> wrote:
On 06/09/2023 18:37 CEST Erik Wienhold <ewie@xxxxxxxxx> wrote:
> Homoglyphs are one explanation if you get 'µass' from the generated column as
> described.
postgres=# SELECT upper('𝝻𝚊𝚜𝚜');
upper
-------
𝝻𝚊𝚜𝚜
(1 row)
The codepoints I picked are:
* MATHEMATICAL SANS-SERIF BOLD SMALL MU
* MATHEMATICAL MONOSPACE SMALL A
* MATHEMATICAL MONOSPACE SMALL S
--
Erik
