pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAbout Unicode IVS
13+ messages / 6 participants
[nested] [flat]
* About Unicode IVS
@ 2022-03-29 03:26 荒井元成 <n2029@ndensan.co.jp>
2022-03-29 03:37 ` Re: About Unicode IVS David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: 荒井元成 @ 2022-03-29 03:26 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi,
In the Length () function, it will be 2 characters where you want it to be 1 character.
Is it possible to respond by changing the settings such as changing the collation setting like SQL Server?
Also, if you understand how to deal with it (eg, create your own function), it would be helpful if you could provide as much information as you can.
Best regards,
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: About Unicode IVS
2022-03-29 03:26 About Unicode IVS 荒井元成 <n2029@ndensan.co.jp>
@ 2022-03-29 03:37 ` David G. Johnston <david.g.johnston@gmail.com>
2022-03-29 04:34 ` RE: About Unicode IVS 荒井元成 <n2029@ndensan.co.jp>
0 siblings, 1 reply; 13+ messages in thread
From: David G. Johnston @ 2022-03-29 03:37 UTC (permalink / raw)
To: 荒井元成 <n2029@ndensan.co.jp>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Monday, March 28, 2022, 荒井元成 <n2029@ndensan.co.jp> wrote:
> Hi,
>
>
>
> In the Length () function, it will be 2 characters where you want it to be
> 1 character.
>
> Is it possible to respond by changing the settings such as changing the collation setting like SQL Server?
>
> Also, if you understand how to deal with it (eg, create your own function), it would be helpful if you could provide as much information as you can.
>
>
>
>
Try char_length(text) instead.
David J.
^ permalink raw reply [nested|flat] 13+ messages in thread
* RE: About Unicode IVS
2022-03-29 03:26 About Unicode IVS 荒井元成 <n2029@ndensan.co.jp>
2022-03-29 03:37 ` Re: About Unicode IVS David G. Johnston <david.g.johnston@gmail.com>
@ 2022-03-29 04:34 ` 荒井元成 <n2029@ndensan.co.jp>
2022-03-29 07:45 ` RE: About Unicode IVS Graham Myers <gmyers@retailexpress.com>
2022-03-29 09:34 ` RE: About Unicode IVS Michel SALAIS <msalais@msym.fr>
0 siblings, 2 replies; 13+ messages in thread
From: 荒井元成 @ 2022-03-29 04:34 UTC (permalink / raw)
To: 'David G. Johnston' <david.g.johnston@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
thank you for your reply.
It will be 2 characters.
select char_length(U&'\+008FBA' || U&'\+0E0102');
char_length
-------------
2
(1 行)
select length('辺󠄂');
length
--------
2
(1 行)
select char_length('辺󠄂');
char_length
-------------
2
(1 行)
$ psql -l
データベース一覧
名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権限
-----------+---------+------------------+----------+-------------------+---------------------
D209007 | D209007 | UTF8 | C | C |
postgres | D209007 | UTF8 | C | C |
template0 | D209007 | UTF8 | C | C | =c/D209007 +
| | | | | D209007=CTc/D209007
template1 | D209007 | UTF8 | C | C | =c/D209007 +
| | | | | D209007=CTc/D209007
(4 行)
$ cat pgdata/PG_VERSION
13
Moto.
From: David G. Johnston <david.g.johnston@gmail.com>
Sent: Tuesday, March 29, 2022 12:38 PM
To: 荒井元成 <n2029@ndensan.co.jp>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: About Unicode IVS
On Monday, March 28, 2022, 荒井元成 <n2029@ndensan.co.jp <mailto:n2029@ndensan.co.jp> > wrote:
Hi,
In the Length () function, it will be 2 characters where you want it to be 1 character.
Is it possible to respond by changing the settings such as changing the collation setting like SQL Server?
Also, if you understand how to deal with it (eg, create your own function), it would be helpful if you could provide as much information as you can.
Try char_length(text) instead.
David J.
^ permalink raw reply [nested|flat] 13+ messages in thread
* RE: About Unicode IVS
2022-03-29 03:26 About Unicode IVS 荒井元成 <n2029@ndensan.co.jp>
2022-03-29 03:37 ` Re: About Unicode IVS David G. Johnston <david.g.johnston@gmail.com>
2022-03-29 04:34 ` RE: About Unicode IVS 荒井元成 <n2029@ndensan.co.jp>
@ 2022-03-29 07:45 ` Graham Myers <gmyers@retailexpress.com>
2022-03-29 08:21 ` RE: About Unicode IVS 荒井元成 <n2029@ndensan.co.jp>
1 sibling, 1 reply; 13+ messages in thread
From: Graham Myers @ 2022-03-29 07:45 UTC (permalink / raw)
To: 荒井元成 <n2029@ndensan.co.jp>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Why do you expect the concatenation of two characters to return a length of
one?
*From:* 荒井元成 <n2029@ndensan.co.jp>
*Sent:* 29 March 2022 05:35
*To:* 'David G. Johnston' <david.g.johnston@gmail.com>
*Cc:* pgsql-admin@lists.postgresql.org
*Subject:* RE: About Unicode IVS
thank you for your reply.
It will be 2 characters.
select char_length(U&'\+008FBA' || U&'\+0E0102');
char_length
-------------
2
(1 行)
select length('辺󠄂');
length
--------
2
(1 行)
select char_length('辺󠄂');
char_length
-------------
2
(1 行)
$ psql -l
データベース一覧
名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権限
-----------+---------+------------------+----------+-------------------+---------------------
D209007 | D209007 | UTF8 | C | C |
postgres | D209007 | UTF8 | C | C |
template0 | D209007 | UTF8 | C | C |
=c/D209007 +
| | | | |
D209007=CTc/D209007
template1 | D209007 | UTF8 | C | C |
=c/D209007 +
| | | | |
D209007=CTc/D209007
(4 行)
$ cat pgdata/PG_VERSION
13
Moto.
*From:* David G. Johnston <david.g.johnston@gmail.com>
*Sent:* Tuesday, March 29, 2022 12:38 PM
*To:* 荒井元成 <n2029@ndensan.co.jp>
*Cc:* pgsql-admin@lists.postgresql.org
*Subject:* Re: About Unicode IVS
Graham Myers
On Monday, March 28, 2022, 荒井元成 <n2029@ndensan.co.jp> wrote:
Hi,
In the Length () function, it will be 2 characters where you want it to be
1 character.
Is it possible to respond by changing the settings such as changing
the collation setting like SQL Server?
Also, if you understand how to deal with it (eg, create your own
function), it would be helpful if you could provide as much
information as you can.
Try char_length(text) instead.
David J.
Attachments:
image564879.png (image/png, 3.7K) download
view image
* RE: About Unicode IVS
2022-03-29 03:26 About Unicode IVS 荒井元成 <n2029@ndensan.co.jp>
2022-03-29 03:37 ` Re: About Unicode IVS David G. Johnston <david.g.johnston@gmail.com>
2022-03-29 04:34 ` RE: About Unicode IVS 荒井元成 <n2029@ndensan.co.jp>
2022-03-29 07:45 ` RE: About Unicode IVS Graham Myers <gmyers@retailexpress.com>
@ 2022-03-29 08:21 ` 荒井元成 <n2029@ndensan.co.jp>
2022-03-29 08:26 ` RE: About Unicode IVS Graham Myers <gmyers@retailexpress.com>
0 siblings, 1 reply; 13+ messages in thread
From: 荒井元成 @ 2022-03-29 08:21 UTC (permalink / raw)
To: 'Graham Myers' <gmyers@retailexpress.com>; 'David G. Johnston' <david.g.johnston@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
thank you for your reply.
This is because two characters display one character.
This includes Unicode Variant Selectors and Combining Characters.
Moto.
From: Graham Myers <gmyers@retailexpress.com>
Sent: Tuesday, March 29, 2022 4:46 PM
To: 荒井元成 <n2029@ndensan.co.jp>; David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: RE: About Unicode IVS
Why do you expect the concatenation of two characters to return a length of one?
Graham Myers
From: 荒井元成 <n2029@ndensan.co.jp <mailto:n2029@ndensan.co.jp> >
Sent: 29 March 2022 05:35
To: 'David G. Johnston' <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com> >
Cc: pgsql-admin@lists.postgresql.org <mailto:pgsql-admin@lists.postgresql.org>
Subject: RE: About Unicode IVS
thank you for your reply.
It will be 2 characters.
select char_length(U&'\+008FBA' || U&'\+0E0102');
char_length
-------------
2
(1 行)
select length('辺󠄂');
length
--------
2
(1 行)
select char_length('辺󠄂');
char_length
-------------
2
(1 行)
$ psql -l
データベース一覧
名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権限
-----------+---------+------------------+----------+-------------------+---------------------
D209007 | D209007 | UTF8 | C | C |
postgres | D209007 | UTF8 | C | C |
template0 | D209007 | UTF8 | C | C | =c/D209007 +
| | | | | D209007=CTc/D209007
template1 | D209007 | UTF8 | C | C | =c/D209007 +
| | | | | D209007=CTc/D209007
(4 行)
$ cat pgdata/PG_VERSION
13
Moto.
From: David G. Johnston <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com> >
Sent: Tuesday, March 29, 2022 12:38 PM
To: 荒井元成 <n2029@ndensan.co.jp <mailto:n2029@ndensan.co.jp> >
Cc: pgsql-admin@lists.postgresql.org <mailto:pgsql-admin@lists.postgresql.org>
Subject: Re: About Unicode IVS
On Monday, March 28, 2022, 荒井元成 <n2029@ndensan.co.jp <mailto:n2029@ndensan.co.jp> > wrote:
Hi,
In the Length () function, it will be 2 characters where you want it to be 1 character.
Is it possible to respond by changing the settings such as changing the collation setting like SQL Server?
Also, if you understand how to deal with it (eg, create your own function), it would be helpful if you could provide as much information as you can.
Try char_length(text) instead.
David J.
Attachments:
image001.png (image/png, 3.1K) download
view image
* RE: About Unicode IVS
2022-03-29 03:26 About Unicode IVS 荒井元成 <n2029@ndensan.co.jp>
2022-03-29 03:37 ` Re: About Unicode IVS David G. Johnston <david.g.johnston@gmail.com>
2022-03-29 04:34 ` RE: About Unicode IVS 荒井元成 <n2029@ndensan.co.jp>
2022-03-29 07:45 ` RE: About Unicode IVS Graham Myers <gmyers@retailexpress.com>
2022-03-29 08:21 ` RE: About Unicode IVS 荒井元成 <n2029@ndensan.co.jp>
@ 2022-03-29 08:26 ` Graham Myers <gmyers@retailexpress.com>
2022-03-29 08:52 ` RE: About Unicode IVS 荒井元成 <n2029@ndensan.co.jp>
0 siblings, 1 reply; 13+ messages in thread
From: Graham Myers @ 2022-03-29 08:26 UTC (permalink / raw)
To: 荒井元成 <n2029@ndensan.co.jp>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Thanks you for the explanation, Unicode always blows my mind 😊 The
problems is that postgres is counting code points which in your example is
two.
*From:* 荒井元成 <n2029@ndensan.co.jp>
*Sent:* 29 March 2022 09:21
*To:* 'Graham Myers' <gmyers@retailexpress.com>; 'David G. Johnston' <
david.g.johnston@gmail.com>
*Cc:* pgsql-admin@lists.postgresql.org
*Subject:* RE: About Unicode IVS
thank you for your reply.
This is because two characters display one character.
This includes Unicode Variant Selectors and Combining Characters.
Moto.
*From:* Graham Myers <gmyers@retailexpress.com>
*Sent:* Tuesday, March 29, 2022 4:46 PM
*To:* 荒井元成 <n2029@ndensan.co.jp>; David G. Johnston <
david.g.johnston@gmail.com>
*Cc:* pgsql-admin@lists.postgresql.org
*Subject:* RE: About Unicode IVS
Why do you expect the concatenation of two characters to return a length of
one?
Graham Myers
*From:* 荒井元成 <n2029@ndensan.co.jp>
*Sent:* 29 March 2022 05:35
*To:* 'David G. Johnston' <david.g.johnston@gmail.com>
*Cc:* pgsql-admin@lists.postgresql.org
*Subject:* RE: About Unicode IVS
thank you for your reply.
It will be 2 characters.
select char_length(U&'\+008FBA' || U&'\+0E0102');
char_length
-------------
2
(1 行)
select length('辺󠄂');
length
--------
2
(1 行)
select char_length('辺󠄂');
char_length
-------------
2
(1 行)
$ psql -l
データベース一覧
名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権限
-----------+---------+------------------+----------+-------------------+---------------------
D209007 | D209007 | UTF8 | C | C |
postgres | D209007 | UTF8 | C | C |
template0 | D209007 | UTF8 | C | C |
=c/D209007 +
| | | | |
D209007=CTc/D209007
template1 | D209007 | UTF8 | C | C |
=c/D209007 +
| | | | |
D209007=CTc/D209007
(4 行)
$ cat pgdata/PG_VERSION
13
Moto.
*From:* David G. Johnston <david.g.johnston@gmail.com>
*Sent:* Tuesday, March 29, 2022 12:38 PM
*To:* 荒井元成 <n2029@ndensan.co.jp>
*Cc:* pgsql-admin@lists.postgresql.org
*Subject:* Re: About Unicode IVS
Graham Myers
On Monday, March 28, 2022, 荒井元成 <n2029@ndensan.co.jp> wrote:
Hi,
In the Length () function, it will be 2 characters where you want it to be
1 character.
Is it possible to respond by changing the settings such as changing
the collation setting like SQL Server?
Also, if you understand how to deal with it (eg, create your own
function), it would be helpful if you could provide as much
information as you can.
Try char_length(text) instead.
David J.
Attachments:
image001.png (image/png, 3.1K) download
view image
image428881.png (image/png, 3.7K) download
view image
* RE: About Unicode IVS
2022-03-29 03:26 About Unicode IVS 荒井元成 <n2029@ndensan.co.jp>
2022-03-29 03:37 ` Re: About Unicode IVS David G. Johnston <david.g.johnston@gmail.com>
2022-03-29 04:34 ` RE: About Unicode IVS 荒井元成 <n2029@ndensan.co.jp>
2022-03-29 07:45 ` RE: About Unicode IVS Graham Myers <gmyers@retailexpress.com>
2022-03-29 08:21 ` RE: About Unicode IVS 荒井元成 <n2029@ndensan.co.jp>
2022-03-29 08:26 ` RE: About Unicode IVS Graham Myers <gmyers@retailexpress.com>
@ 2022-03-29 08:52 ` 荒井元成 <n2029@ndensan.co.jp>
0 siblings, 0 replies; 13+ messages in thread
From: 荒井元成 @ 2022-03-29 08:52 UTC (permalink / raw)
To: 'Graham Myers' <gmyers@retailexpress.com>; 'David G. Johnston' <david.g.johnston@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Where should I make a request if I want Postgresql to handle it?
Is this mailing list all right?
Moto.
From: Graham Myers <gmyers@retailexpress.com>
Sent: Tuesday, March 29, 2022 5:26 PM
To: 荒井元成 <n2029@ndensan.co.jp>; David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: RE: About Unicode IVS
Thanks you for the explanation, Unicode always blows my mind 😊 The problems is that postgres is counting code points which in your example is two.
Graham Myers
From: 荒井元成 <n2029@ndensan.co.jp <mailto:n2029@ndensan.co.jp> >
Sent: 29 March 2022 09:21
To: 'Graham Myers' <gmyers@retailexpress.com <mailto:gmyers@retailexpress.com> >; 'David G. Johnston' <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com> >
Cc: pgsql-admin@lists.postgresql.org <mailto:pgsql-admin@lists.postgresql.org>
Subject: RE: About Unicode IVS
thank you for your reply.
This is because two characters display one character.
This includes Unicode Variant Selectors and Combining Characters.
Moto.
From: Graham Myers <gmyers@retailexpress.com <mailto:gmyers@retailexpress.com> >
Sent: Tuesday, March 29, 2022 4:46 PM
To: 荒井元成 <n2029@ndensan.co.jp <mailto:n2029@ndensan.co.jp> >; David G. Johnston <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com> >
Cc: pgsql-admin@lists.postgresql.org <mailto:pgsql-admin@lists.postgresql.org>
Subject: RE: About Unicode IVS
Why do you expect the concatenation of two characters to return a length of one?
Graham Myers
From: 荒井元成 <n2029@ndensan.co.jp <mailto:n2029@ndensan.co.jp> >
Sent: 29 March 2022 05:35
To: 'David G. Johnston' <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com> >
Cc: pgsql-admin@lists.postgresql.org <mailto:pgsql-admin@lists.postgresql.org>
Subject: RE: About Unicode IVS
thank you for your reply.
It will be 2 characters.
select char_length(U&'\+008FBA' || U&'\+0E0102');
char_length
-------------
2
(1 行)
select length('辺󠄂');
length
--------
2
(1 行)
select char_length('辺󠄂');
char_length
-------------
2
(1 行)
$ psql -l
データベース一覧
名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権限
-----------+---------+------------------+----------+-------------------+---------------------
D209007 | D209007 | UTF8 | C | C |
postgres | D209007 | UTF8 | C | C |
template0 | D209007 | UTF8 | C | C | =c/D209007 +
| | | | | D209007=CTc/D209007
template1 | D209007 | UTF8 | C | C | =c/D209007 +
| | | | | D209007=CTc/D209007
(4 行)
$ cat pgdata/PG_VERSION
13
Moto.
From: David G. Johnston <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com> >
Sent: Tuesday, March 29, 2022 12:38 PM
To: 荒井元成 <n2029@ndensan.co.jp <mailto:n2029@ndensan.co.jp> >
Cc: pgsql-admin@lists.postgresql.org <mailto:pgsql-admin@lists.postgresql.org>
Subject: Re: About Unicode IVS
On Monday, March 28, 2022, 荒井元成 <n2029@ndensan.co.jp <mailto:n2029@ndensan.co.jp> > wrote:
Hi,
In the Length () function, it will be 2 characters where you want it to be 1 character.
Is it possible to respond by changing the settings such as changing the collation setting like SQL Server?
Also, if you understand how to deal with it (eg, create your own function), it would be helpful if you could provide as much information as you can.
Try char_length(text) instead.
David J.
Attachments:
image002.png (image/png, 3.1K) download
view image
image003.png (image/png, 3.1K) download
view image
* RE: About Unicode IVS
2022-03-29 03:26 About Unicode IVS 荒井元成 <n2029@ndensan.co.jp>
2022-03-29 03:37 ` Re: About Unicode IVS David G. Johnston <david.g.johnston@gmail.com>
2022-03-29 04:34 ` RE: About Unicode IVS 荒井元成 <n2029@ndensan.co.jp>
@ 2022-03-29 09:34 ` Michel SALAIS <msalais@msym.fr>
2022-03-29 09:55 ` RE: About Unicode IVS 荒井元成 <n2029@ndensan.co.jp>
1 sibling, 1 reply; 13+ messages in thread
From: Michel SALAIS @ 2022-03-29 09:34 UTC (permalink / raw)
To: '荒井元成' <n2029@ndensan.co.jp>; 'David G. Johnston' <david.g.johnston@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Hi,
I think this has something to do with collation and ctype. As I see you have it set to “C” for all your databases (even if I don’t understand your titles 😊).
Michel SALAIS
De : 荒井元成 <n2029@ndensan.co.jp>
Envoyé : mardi 29 mars 2022 06:35
À : 'David G. Johnston' <david.g.johnston@gmail.com>
Cc : pgsql-admin@lists.postgresql.org
Objet : RE: About Unicode IVS
thank you for your reply.
It will be 2 characters.
select char_length(U&'\+008FBA' || U&'\+0E0102');
char_length
-------------
2
(1 行)
select length('辺󠄂');
length
--------
2
(1 行)
select char_length('辺󠄂');
char_length
-------------
2
(1 行)
$ psql -l
データベース一覧
名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権限
-----------+---------+------------------+----------+-------------------+---------------------
D209007 | D209007 | UTF8 | C | C |
postgres | D209007 | UTF8 | C | C |
template0 | D209007 | UTF8 | C | C | =c/D209007 +
| | | | | D209007=CTc/D209007
template1 | D209007 | UTF8 | C | C | =c/D209007 +
| | | | | D209007=CTc/D209007
(4 行)
$ cat pgdata/PG_VERSION
13
Moto.
From: David G. Johnston <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com> >
Sent: Tuesday, March 29, 2022 12:38 PM
To: 荒井元成 <n2029@ndensan.co.jp <mailto:n2029@ndensan.co.jp> >
Cc: pgsql-admin@lists.postgresql.org <mailto:pgsql-admin@lists.postgresql.org>
Subject: Re: About Unicode IVS
On Monday, March 28, 2022, 荒井元成 <n2029@ndensan.co.jp <mailto:n2029@ndensan.co.jp> > wrote:
Hi,
In the Length () function, it will be 2 characters where you want it to be 1 character.
Is it possible to respond by changing the settings such as changing the collation setting like SQL Server?
Also, if you understand how to deal with it (eg, create your own function), it would be helpful if you could provide as much information as you can.
Try char_length(text) instead.
David J.
^ permalink raw reply [nested|flat] 13+ messages in thread
* RE: About Unicode IVS
2022-03-29 03:26 About Unicode IVS 荒井元成 <n2029@ndensan.co.jp>
2022-03-29 03:37 ` Re: About Unicode IVS David G. Johnston <david.g.johnston@gmail.com>
2022-03-29 04:34 ` RE: About Unicode IVS 荒井元成 <n2029@ndensan.co.jp>
2022-03-29 09:34 ` RE: About Unicode IVS Michel SALAIS <msalais@msym.fr>
@ 2022-03-29 09:55 ` 荒井元成 <n2029@ndensan.co.jp>
2022-03-29 10:02 ` Re: About Unicode IVS Holger Jakobs <holger@jakobs.com>
0 siblings, 1 reply; 13+ messages in thread
From: 荒井元成 @ 2022-03-29 09:55 UTC (permalink / raw)
To: 'Michel SALAIS' <msalais@msym.fr>; 'David G. Johnston' <david.g.johnston@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
thank you for your reply.
Changing the collation order and CTYPE did not change the behavior.
名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権限
-----------+---------+------------------+-------------+-------------------+---------------------
D209007 | D209007 | UTF8 | C | C |
postgres | D209007 | UTF8 | C | C |
template0 | D209007 | UTF8 | C | C | =c/D209007 +
| | | | | D209007=CTc/D209007
template1 | D209007 | UTF8 | C | C | =c/D209007 +
| | | | | D209007=CTc/D209007
template2 | D209007 | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
(5 行)
D209007=# \c template2
データベース"template2"にユーザ"D209007"として接続しました。
template2=# select char_length(U&'\+0000E6' || U&'\+000300');
char_length
-------------
2
(1 行)
template2=# select char_length(U&'\+008FBA' || U&'\+0E0102');
char_length
-------------
2
(1 行)
template2=# select length(U&'\+008FBA' || U&'\+0E0102');
length
--------
2
(1 行)
Moto.
From: Michel SALAIS <msalais@msym.fr>
Sent: Tuesday, March 29, 2022 6:35 PM
To: '荒井元成' <n2029@ndensan.co.jp>; 'David G. Johnston' <david.g.johnston@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: RE: About Unicode IVS
Hi,
I think this has something to do with collation and ctype. As I see you have it set to “C” for all your databases (even if I don’t understand your titles 😊).
Michel SALAIS
De : 荒井元成 <n2029@ndensan.co.jp <mailto:n2029@ndensan.co.jp> >
Envoyé : mardi 29 mars 2022 06:35
À : 'David G. Johnston' <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com> >
Cc : pgsql-admin@lists.postgresql.org <mailto:pgsql-admin@lists.postgresql.org>
Objet : RE: About Unicode IVS
thank you for your reply.
It will be 2 characters.
select char_length(U&'\+008FBA' || U&'\+0E0102');
char_length
-------------
2
(1 行)
select length('辺󠄂');
length
--------
2
(1 行)
select char_length('辺󠄂');
char_length
-------------
2
(1 行)
$ psql -l
データベース一覧
名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権限
-----------+---------+------------------+----------+-------------------+---------------------
D209007 | D209007 | UTF8 | C | C |
postgres | D209007 | UTF8 | C | C |
template0 | D209007 | UTF8 | C | C | =c/D209007 +
| | | | | D209007=CTc/D209007
template1 | D209007 | UTF8 | C | C | =c/D209007 +
| | | | | D209007=CTc/D209007
(4 行)
$ cat pgdata/PG_VERSION
13
Moto.
From: David G. Johnston <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com> >
Sent: Tuesday, March 29, 2022 12:38 PM
To: 荒井元成 <n2029@ndensan.co.jp <mailto:n2029@ndensan.co.jp> >
Cc: pgsql-admin@lists.postgresql.org <mailto:pgsql-admin@lists.postgresql.org>
Subject: Re: About Unicode IVS
On Monday, March 28, 2022, 荒井元成 <n2029@ndensan.co.jp <mailto:n2029@ndensan.co.jp> > wrote:
Hi,
In the Length () function, it will be 2 characters where you want it to be 1 character.
Is it possible to respond by changing the settings such as changing the collation setting like SQL Server?
Also, if you understand how to deal with it (eg, create your own function), it would be helpful if you could provide as much information as you can.
Try char_length(text) instead.
David J.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: About Unicode IVS
2022-03-29 03:26 About Unicode IVS 荒井元成 <n2029@ndensan.co.jp>
2022-03-29 03:37 ` Re: About Unicode IVS David G. Johnston <david.g.johnston@gmail.com>
2022-03-29 04:34 ` RE: About Unicode IVS 荒井元成 <n2029@ndensan.co.jp>
2022-03-29 09:34 ` RE: About Unicode IVS Michel SALAIS <msalais@msym.fr>
2022-03-29 09:55 ` RE: About Unicode IVS 荒井元成 <n2029@ndensan.co.jp>
@ 2022-03-29 10:02 ` Holger Jakobs <holger@jakobs.com>
2022-03-29 10:25 ` Re: About Unicode IVS Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Holger Jakobs @ 2022-03-29 10:02 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
It's totally correct that the two characters are still two characters.
You would have to normalize the string first, so that the combination
becomes one character.
More information about this topic, which is in part beyond PostgreSQL:
* https://stackoverflow.com/questions/7931204/what-is-normalized-utf-8-all-about
* https://en.wikipedia.org/wiki/Unicode_equivalence
Regards,
Holger
Am 29.03.22 um 11:55 schrieb 荒井元成:
>
> thank you for your reply.
>
> Changing the collation order and CTYPE did not change the behavior.
>
> 名前| 所有者 | エンコーディング| 照合順序 | Ctype(変換演算子) |
> アクセス権限
>
> -----------+---------+------------------+-------------+-------------------+---------------------
>
> D209007 | D209007 | UTF8 | C | C |
>
> postgres | D209007 | UTF8 | C | C |
>
> template0 | D209007 | UTF8 | C | C |
> =c/D209007 +
>
> | | | | | D209007=CTc/D209007
>
> template1 | D209007 | UTF8 | C | C |
> =c/D209007 +
>
> | | | | | D209007=CTc/D209007
>
> template2 | D209007 | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
>
> (5 行)
>
> D209007=# \c template2
>
> データベース"template2"にユーザ"D209007"として接続しました。
>
> template2=# select char_length(U&'\+0000E6' || U&'\+000300');
>
> char_length
>
> -------------
>
> 2
>
> (1 行)
>
> template2=# select char_length(U&'\+008FBA' || U&'\+0E0102');
>
> char_length
>
> -------------
>
> 2
>
> (1 行)
>
> template2=# select length(U&'\+008FBA' || U&'\+0E0102');
>
> length
>
> --------
>
> 2
>
> (1 行)
>
> Moto.
>
> *From:*Michel SALAIS <msalais@msym.fr>
> *Sent:* Tuesday, March 29, 2022 6:35 PM
> *To:* '荒井元成' <n2029@ndensan.co.jp>; 'David G. Johnston'
> <david.g.johnston@gmail.com>
> *Cc:* pgsql-admin@lists.postgresql.org
> *Subject:* RE: About Unicode IVS
>
> Hi,
>
> I think this has something to do with collation and ctype. As I see
> you have it set to “C” for all your databases (even if I don’t
> understand your titles 😊).
>
> /Michel SALAIS/
>
> //
>
> *De :*荒井元成<n2029@ndensan.co.jp>
> *Envoyé :* mardi 29 mars 2022 06:35
> *À :* 'David G. Johnston' <david.g.johnston@gmail.com>
> *Cc :* pgsql-admin@lists.postgresql.org
> *Objet :* RE: About Unicode IVS
>
> thank you for your reply.
>
> It will be 2 characters.
>
> select char_length(U&'\+008FBA' || U&'\+0E0102');
>
> char_length
>
> -------------
>
> 2
>
> (1 行)
>
> select length('辺󠄂');
>
> length
>
> --------
>
> 2
>
> (1 行)
>
> select char_length('辺󠄂');
>
> char_length
>
> -------------
>
> 2
>
> (1 行)
>
> $ psql -l
>
> データベース一覧
>
> 名前| 所有者| エンコーディング| 照合順序| Ctype(変換演算子) |アクセス権限
>
> -----------+---------+------------------+----------+-------------------+---------------------
>
> D209007| D209007 | UTF8| C| C|
>
> postgres| D209007 | UTF8| C| C|
>
> template0 | D209007 | UTF8| C| C| =c/D209007+
>
> ||||| D209007=CTc/D209007
>
> template1 | D209007 | UTF8| C| C| =c/D209007+
>
> ||||| D209007=CTc/D209007
>
> (4 行)
>
> $ cat pgdata/PG_VERSION
>
> 13
>
> Moto.
>
> *From:*David G. Johnston <david.g.johnston@gmail.com>
> *Sent:* Tuesday, March 29, 2022 12:38 PM
> *To:* 荒井元成<n2029@ndensan.co.jp>
> *Cc:* pgsql-admin@lists.postgresql.org
> *Subject:* Re: About Unicode IVS
>
>
>
> On Monday, March 28, 2022, 荒井元成<n2029@ndensan.co.jp> wrote:
>
> Hi,
>
> In the Length () function, it will be 2 characters where you want
> it to be 1 character.
>
> Is it possible to respond by changing the settings such as
> changing the collation setting like SQL Server?
>
> Also, if you understand how to deal with it (eg, create your own
> function), it would be helpful if you could provide as much
> information as you can.
>
> Try char_length(text) instead.
>
> David J.
>
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachments:
OpenPGP_signature (application/pgp-signature, 202B) download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: About Unicode IVS
2022-03-29 03:26 About Unicode IVS 荒井元成 <n2029@ndensan.co.jp>
2022-03-29 03:37 ` Re: About Unicode IVS David G. Johnston <david.g.johnston@gmail.com>
2022-03-29 04:34 ` RE: About Unicode IVS 荒井元成 <n2029@ndensan.co.jp>
2022-03-29 09:34 ` RE: About Unicode IVS Michel SALAIS <msalais@msym.fr>
2022-03-29 09:55 ` RE: About Unicode IVS 荒井元成 <n2029@ndensan.co.jp>
2022-03-29 10:02 ` Re: About Unicode IVS Holger Jakobs <holger@jakobs.com>
@ 2022-03-29 10:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-03-29 11:03 ` RE: About Unicode IVS 荒井元成 <n2029@ndensan.co.jp>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2022-03-29 10:25 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin@lists.postgresql.org; n2029@ndensan.co.jp
Holger Jakobs <holger@jakobs.com> writes:
> It's totally correct that the two characters are still two characters.
> You would have to normalize the string first, so that the combination
> becomes one character.
Yeah. In principle the normalize() function ought to do this for
you. But it doesn't seem to shorten the given example for me;
I'm not sure if that means the example is incorrect, or if it's
a bug in normalize().
u8=# select octet_length(U&'\+008FBA' || U&'\+0E0102');
octet_length
--------------
7
(1 row)
u8=# select octet_length(normalize(U&'\+008FBA' || U&'\+0E0102'));
octet_length
--------------
7
(1 row)
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* RE: About Unicode IVS
2022-03-29 03:26 About Unicode IVS 荒井元成 <n2029@ndensan.co.jp>
2022-03-29 03:37 ` Re: About Unicode IVS David G. Johnston <david.g.johnston@gmail.com>
2022-03-29 04:34 ` RE: About Unicode IVS 荒井元成 <n2029@ndensan.co.jp>
2022-03-29 09:34 ` RE: About Unicode IVS Michel SALAIS <msalais@msym.fr>
2022-03-29 09:55 ` RE: About Unicode IVS 荒井元成 <n2029@ndensan.co.jp>
2022-03-29 10:02 ` Re: About Unicode IVS Holger Jakobs <holger@jakobs.com>
2022-03-29 10:25 ` Re: About Unicode IVS Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-03-29 11:03 ` 荒井元成 <n2029@ndensan.co.jp>
0 siblings, 0 replies; 13+ messages in thread
From: 荒井元成 @ 2022-03-29 11:03 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; 'Holger Jakobs' <holger@jakobs.com>; +Cc: pgsql-admin@lists.postgresql.org
thank you for your reply.
In SQL Server, the variant character selector is treated as one character with two characters. The collation order is Japanese_XJIS_140_CS_AS_KS_WS_VSS_UTF8.
Moto.
-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Tuesday, March 29, 2022 7:26 PM
To: Holger Jakobs <holger@jakobs.com>
Cc: pgsql-admin@lists.postgresql.org; n2029@ndensan.co.jp
Subject: Re: About Unicode IVS
Holger Jakobs <holger@jakobs.com> writes:
> It's totally correct that the two characters are still two characters.
> You would have to normalize the string first, so that the combination
> becomes one character.
Yeah. In principle the normalize() function ought to do this for you. But it doesn't seem to shorten the given example for me; I'm not sure if that means the example is incorrect, or if it's a bug in normalize().
u8=# select octet_length(U&'\+008FBA' || U&'\+0E0102'); octet_length
--------------
7
(1 row)
u8=# select octet_length(normalize(U&'\+008FBA' || U&'\+0E0102')); octet_length
--------------
7
(1 row)
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* RE: Re: About Unicode IVS
@ 2022-03-30 00:06 荒井元成 <n2029@ndensan.co.jp>
0 siblings, 0 replies; 13+ messages in thread
From: 荒井元成 @ 2022-03-30 00:06 UTC (permalink / raw)
To: 'Holger Jakobs' <holger@jakobs.com>; +Cc: pgsql-admin@lists.postgresql.org
Variant forms cannot be solved by normalization.
Moto.
-------- 元のメッセージ --------
件名: Re: About Unicode IVS
日付: 2022-03-29 19:02
発信者: Holger Jakobs <holger@jakobs.com>
宛先: pgsql-admin@lists.postgresql.org
It's totally correct that the two characters are still two characters.
You would have to normalize the string first, so that the combination becomes one character.
More information about this topic, which is in part beyond PostgreSQL:
*
https://stackoverflow.com/questions/7931204/what-is-normalized-utf-8-all-about
[1]
* https://en.wikipedia.org/wiki/Unicode_equivalence [2]
Regards,
Holger
Am 29.03.22 um 11:55 schrieb 荒井元成:
> thank you for your reply.
>
> Changing the collation order and CTYPE did not change the behavior.
>
> 名前 | 所有者 | エンコーディング | 照合順序
> | Ctype(変換演算子) | アクセス権限
>
>
-----------+---------+------------------+-------------+-------------------+---------------------
>
>
> D209007 | D209007 | UTF8 | C | C
> |
>
> postgres | D209007 | UTF8 | C | C
> |
>
> template0 | D209007 | UTF8 | C | C
> | =c/D209007 +
>
> | | | |
> | D209007=CTc/D209007
>
> template1 | D209007 | UTF8 | C | C
> | =c/D209007 +
>
> | | | |
> | D209007=CTc/D209007
>
> template2 | D209007 | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8
> |
>
> (5 行)
>
> D209007=# c template2
>
>
データベース"template2"にユーザ"D209007"として接続しました。
>
>
> template2=# select char_length(U&'+0000E6' || U&'+000300');
>
> char_length
>
> -------------
>
> 2
>
> (1 行)
>
> template2=# select char_length(U&'+008FBA' || U&'+0E0102');
>
> char_length
>
> -------------
>
> 2
>
> (1 行)
>
> template2=# select length(U&'+008FBA' || U&'+0E0102');
>
> length
>
> --------
>
> 2
>
> (1 行)
>
> Moto.
>
> FROM: Michel SALAIS <msalais@msym.fr>
> SENT: Tuesday, March 29, 2022 6:35 PM
> TO: '荒井元成' <n2029@ndensan.co.jp>; 'David G. Johnston'
> <david.g.johnston@gmail.com>
> CC: pgsql-admin@lists.postgresql.org
> SUBJECT: RE: About Unicode IVS
>
> Hi,
>
> I think this has something to do with collation and ctype. As I see
> you have it set to “C” for all your databases (even if I don’t
> understand your titles 😊).
>
> _Michel SALAIS_
>
> _ _
>
> DE : 荒井元成 <n2029@ndensan.co.jp>
> ENVOYÉ : mardi 29 mars 2022 06:35
> À : 'David G. Johnston' <david.g.johnston@gmail.com> CC :
> pgsql-admin@lists.postgresql.org OBJET : RE: About Unicode IVS
>
> thank you for your reply.
>
> It will be 2 characters.
>
> select char_length(U&'+008FBA' || U&'+0E0102');
>
> char_length
>
> -------------
>
> 2
>
> (1 行)
>
> select length('辺󠄂');
>
> length
>
> --------
>
> 2
>
> (1 行)
>
> select char_length('辺󠄂');
>
> char_length
>
> -------------
>
> 2
>
> (1 行)
>
> $ psql -l
>
> データベース一覧
>
> 名前 | 所有者 | エンコーディング | 照合順序 |
> Ctype(変換演算子) | アクセス権限
>
>
-----------+---------+------------------+----------+-------------------+---------------------
>
>
> D209007 | D209007 | UTF8 | C | C
> |
>
> postgres | D209007 | UTF8 | C | C
> |
>
> template0 | D209007 | UTF8 | C | C
> | =c/D209007 +
>
> | | | |
> | D209007=CTc/D209007
>
> template1 | D209007 | UTF8 | C | C
> | =c/D209007 +
>
> | | | |
> | D209007=CTc/D209007
>
> (4 行)
>
> $ cat pgdata/PG_VERSION
>
> 13
>
> Moto.
>
> FROM: David G. Johnston <david.g.johnston@gmail.com>
> SENT: Tuesday, March 29, 2022 12:38 PM
> TO: 荒井元成 <n2029@ndensan.co.jp>
> CC: pgsql-admin@lists.postgresql.org
> SUBJECT: Re: About Unicode IVS
>
> On Monday, March 28, 2022, 荒井元成 <n2029@ndensan.co.jp> wrote:
>
>> Hi,
>>
>> In the Length () function, it will be 2 characters where you want it
>> to be 1 character.
>>
>> Is it possible to respond by changing the settings such as changing
>> the collation setting like SQL Server?
>>
>> Also, if you understand how to deal with it (eg, create your own
>> function), it would be helpful if you could provide as much
>> information as you can.
>
> Try char_length(text) instead.
>
> David J.
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Links:
------
[1]
https://stackoverflow.com/questions/7931204/what-is-normalized-utf-8-all-about
[2] https://en.wikipedia.org/wiki/Unicode_equivalence
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2022-03-30 00:06 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-03-29 03:26 About Unicode IVS 荒井元成 <n2029@ndensan.co.jp>
2022-03-29 03:37 ` David G. Johnston <david.g.johnston@gmail.com>
2022-03-29 04:34 ` 荒井元成 <n2029@ndensan.co.jp>
2022-03-29 07:45 ` Graham Myers <gmyers@retailexpress.com>
2022-03-29 08:21 ` 荒井元成 <n2029@ndensan.co.jp>
2022-03-29 08:26 ` Graham Myers <gmyers@retailexpress.com>
2022-03-29 08:52 ` 荒井元成 <n2029@ndensan.co.jp>
2022-03-29 09:34 ` Michel SALAIS <msalais@msym.fr>
2022-03-29 09:55 ` 荒井元成 <n2029@ndensan.co.jp>
2022-03-29 10:02 ` Holger Jakobs <holger@jakobs.com>
2022-03-29 10:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-03-29 11:03 ` 荒井元成 <n2029@ndensan.co.jp>
2022-03-30 00:06 RE: Re: About Unicode IVS 荒井元成 <n2029@ndensan.co.jp>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox