Postgres Pro
Downloads
Home   >   mailing lists

Re: The same result for with SPACE and without SPACE - Mailing list pgsql-admin

From David G. Johnston
Subject Re: The same result for with SPACE and without SPACE
Date
Msg-id CAKFQuwY8PhVM9f3V=ojqd77mggPXhywLVUo_JU=BE1sg-e33TQ@mail.gmail.com
Whole thread Raw
In response to Re: The same result for with SPACE and without SPACE  (M Sarwar <sarwarmd02@outlook.com>)
Responses Re: The same result for with SPACE and without SPACE
List pgsql-admin
Tree view
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 by date:

Previous
From: M Sarwar
Date:
Subject: Re: The same result for with SPACE and without SPACE
Next
From: Flavio Henrique Araque Gurgel
Date:
Subject: Re: is there pgadmin interface with Hashicorp Vault or command line interface to use master password and update password repository