From: | M Sarwar <sarwarmd02(at)outlook(dot)com> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | Scott Ribe <scott_ribe(at)elevated-dev(dot)com>, "Wetmore, Matthew (CTR)" <Matthew(dot)Wetmore(at)express-scripts(dot)com>, mahesh <mahesha_dba(at)yahoo(dot)com>, Mohammed Aslam <hmdaslam97(at)gmail(dot)com>, "pgsql-admin(at)lists(dot)postgresql(dot)org" <pgsql-admin(at)lists(dot)postgresql(dot)org>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Subject: | Re: The same result for with SPACE and without SPACE |
Date: | 2023-06-15 22:50:12 |
Message-ID: | DM4PR19MB5978011B7B0F050B0E6D46DCD35BA@DM4PR19MB5978.namprd19.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-advocacy |
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(dot)g(dot)johnston(at)gmail(dot)com>
Sent: Thursday, June 15, 2023 2:04 PM
To: M Sarwar <sarwarmd02(at)outlook(dot)com>
Cc: Scott Ribe <scott_ribe(at)elevated-dev(dot)com>; Wetmore, Matthew (CTR) <Matthew(dot)Wetmore(at)express-scripts(dot)com>; mahesh <mahesha_dba(at)yahoo(dot)com>; Mohammed Aslam <hmdaslam97(at)gmail(dot)com>; pgsql-admin(at)lists(dot)postgresql(dot)org <pgsql-admin(at)lists(dot)postgresql(dot)org>; Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: The same result for with SPACE and without SPACE
On Thu, Jun 15, 2023 at 10:44 AM M Sarwar <sarwarmd02(at)outlook(dot)com<mailto:sarwarmd02(at)outlook(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | M Sarwar | 2023-06-16 02:23:27 | Conversion from Number to Date |
Previous Message | Flavio Henrique Araque Gurgel | 2023-06-15 20:15:19 | Re: is there pgadmin interface with Hashicorp Vault or command line interface to use master password and update password repository |
From | Date | Subject | |
---|---|---|---|
Next Message | Jacob Champion | 2023-08-01 18:59:39 | Timescale's State of PostgreSQL 2023 Survey |
Previous Message | David G. Johnston | 2023-06-15 18:04:41 | Re: The same result for with SPACE and without SPACE |