Re: The same result for with SPACE and without SPACE

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]


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.
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?


From: Scott Ribe <scott_ribe@xxxxxxxxxxxxxxxx>
Sent: Thursday, June 15, 2023 11:56 AM
To: Wetmore, Matthew (CTR) <Matthew.Wetmore@xxxxxxxxxxxxxxxxxxx>
Cc: mahesh <mahesha_dba@xxxxxxxxx>; Mohammed Aslam <hmdaslam97@xxxxxxxxx>; pgsql-admin@xxxxxxxxxxxxxxxxxxxx <pgsql-admin@xxxxxxxxxxxxxxxxxxxx>; Tom Lane <tgl@xxxxxxxxxxxxx>; M Sarwar <sarwarmd02@xxxxxxxxxxx>
Subject: Re: The same result for with SPACE and without SPACE
Nobody's kicking you out of any group. Someone requested themselves to be removed, with a typo that made it ambiguous.

The original question had nothing to do with INT, it was behavior of CHAR and trailing spaces.

> select c1 from matt_test where c1 = '123'
> -- all 3 rows returned.
>  Is it expected behavior that all 3 rows would be returned (because the space isn’t an INT?)

Yes. Or more precisely, it is because when a string is cast to an int, leading and trailing spaces are ignored. The alternative would be to raise an error, as 'an integer plus some spaces' is not an integer...

> select c2 from matt_test2 where c2 = '123'
> -- 1 rows returned.

Yes, for TEXT column, which behaves the same as VARCHAR. Also 1 row for:

select c2 from matt_test2 where c2 = '123 '

But 2 rows returned for CHAR column, as inserting '123' and '123 ' into CHAR(n) results in the same value being inserted. And also 2 rows returned for:

select c2 from matt_test2 where c2 = '123 '

^^^ which was the original question

[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux