Re: The same result for with SPACE and without SPACE

Поиск
Список
Период
Сортировка
От M Sarwar
Тема Re: The same result for with SPACE and without SPACE
Дата
Msg-id DM4PR19MB5978011B7B0F050B0E6D46DCD35BA@DM4PR19MB5978.namprd19.prod.outlook.com
обсуждение исходный текст
Ответ на Re: The same result for with SPACE and without SPACE  ("David G. Johnston" <david.g.johnston@gmail.com>)
Список pgsql-admin
Дерево обсуждения
Hi David,
I appreciate your response.

I did the analysis.
It sounds me that in realistic scenario, CHAR_10 and VAR_CHAR_10 columns with the same data will fetch the desired results. It sounds me that there is no conflict.

select

                                length( '123'::char(5)),

                                length( '123  '::char(5)),

                                length('123'::varchar),

                                length('123    '::varchar),

                                length(('123  '::char(5))::varchar),

                               '123  '::char(5) =           '123     '::varchar,

                                ('123  '::char(5))::varchar = '123     '::varchar     --- this is FALSE which is fine because VARCHAR is not padding the space.

;


Output:- 

3              3              3              7              3              true       false


  Thanks,

Sarwar

From: David G. Johnston <david.g.johnston@gmail.com>
Sent: Thursday, June 15, 2023 2:04 PM
To: M Sarwar <sarwarmd02@outlook.com>
Cc: Scott Ribe <scott_ribe@elevated-dev.com>; Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>; mahesh <mahesha_dba@yahoo.com>; Mohammed Aslam <hmdaslam97@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: The same result for with SPACE and without SPACE
 
On Thu, Jun 15, 2023 at 10:44 AM M Sarwar <sarwarmd02@outlook.com> wrote:
Hello Scott and all,

Here the question comes with respect to CHAR(10) to CHARACTeR VARYING( 10 ) comparison results.
char_10   - type character ( 10 )
var_char_10 - type character varying ( 10)

When I do the comparison between char_10 and var_char_10 columns, I may get the wrong results.
var_char_10 is always trimmed from right.

NO.  varchar is never trimmed in this sense.  Though casting to a varchar(n) will result in truncation of the input value to n characters - regardless of what those characters are.


char_10 has padded data of blank or spaces.
Now I compare char10 and var_char_10 columns, I will get the wrong results because char_10 has padded spaces.

Is that correct or will it ignore whitespaces at the end of char_10 column?


Testing shows that the varchar value gets promoted to char, not the other way around.

postgres=# select '123  '::char(5) = '123    '::varchar;
 ?column?
----------
 t
(1 row)

postgres=# select ('123  '::char(5))::varchar = '123    '::varchar;
 ?column?
----------
 f
(1 row)

No one memorizes char behavior - if you must use it then test your code, and maybe be extra explicit with your casting too.

David J.

В списке pgsql-admin по дате отправления: