Обсуждение: The same result for with SPACE and without SPACE

Did you use type char instead of varchar?

M Sarwar <sarwarmd02@outlook.com> writes:
> I expect this to behave the same way whether it comes from CHAR or VARCHAR.

You expect incorrectly.  CHAR comparison is insensitive to trailing
spaces:

https://www.postgresql.org/docs/current/datatype-character.html

            regards, tom lane

> On Jun 14, 2023, at 4:31 PM, M Sarwar <sarwarmd02@outlook.com> wrote:
>
> I expect this to behave the same way whether it comes from CHAR or VARCHAR.

Why would you expect that??? CHAR explicitly means that:

1) shorter strings will be space-padded to the given length
2) if a string is presented with spaces making it longer, it will be truncated

Putting it all together, it implies:

3) trailing spaces are semantically meaningless

Anyway, the closest I could to finding a reference on comparison behavior for this case states: "The ANSI standard
requirespadding for the character strings used in comparisons so that their lengths match before comparing them." 

And there you go, you used CHAR(10), so all values in the table are space-padded to length 10, so for comparison *any*
valuewill be space-padded to 10. 

One more followup comment: CHAR is rather an archaic vestige, from a time when fixed-length vs variable-length might
measurablyimprove performance somewhere, or removing the requirement to store a length with every instance might help
withspace requirements. There is really almost no anymore to ever use it for fixed length strings. There is *NO* good
reasonthat I know of to ever use it for values which are not actually fixed-length, that is assuredly a mistake. 

I can reproduce this with a INT

-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Wednesday, June 14, 2023 3:46 PM
To: M Sarwar <sarwarmd02@outlook.com>
Cc: Scott Ribe <scott_ribe@elevated-dev.com>; pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Re: The same result for with SPACE and without SPACE

M Sarwar <sarwarmd02@outlook.com> writes:
> I expect this to behave the same way whether it comes from CHAR or VARCHAR.

You expect incorrectly.  CHAR comparison is insensitive to trailing
spaces:


https://urldefense.com/v3/__https://www.postgresql.org/docs/current/datatype-character.html__;!!GFE8dS6aclb0h1nkhPf9!5ahVPSTPuRxIvywc-UhqjYfqPmkGYEBT5b4GYndHqVaD0G98F0LrX6G6GgTNaJsMVaewVB5e1wox__K0nNGLZsEilYBU$


            regards, tom lane

> On Jun 15, 2023, at 7:08 AM, Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com> wrote:
>
> I can reproduce this with a INT

?

When casting string to integer, trailing spaces do not cause an exception. When casting integer to char(n), the string
willbe blank padded. 

@font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;}@font-face {font-family:"Helvetica Neue";}p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0in; margin-bottom:.0001pt; font-size:12.0pt; font-family:"Times New Roman",serif;}a:link, span.MsoHyperlink {mso-style-priority:99; color:blue; text-decoration:underline;}a:visited, span.MsoHyperlinkFollowed {mso-style-priority:99; color:purple; text-decoration:underline;}p.msonormal0, li.msonormal0, div.msonormal0 {mso-style-name:msonormal; mso-margin-top-alt:auto; margin-right:0in; mso-margin-bottom-alt:auto; margin-left:0in; font-size:12.0pt; font-family:"Times New Roman",serif;}span.EmailStyle18 {mso-style-type:personal-reply; font-family:"Calibri",sans-serif; color:#1F497D;}.MsoChpDefault {mso-style-type:export-only; font-size:10.0pt;}div.WordSection1 {page:WordSection1;}

Before you kick me out of the group, can you please explain.

 

I thought the orig issue was that purposefully spaces/whitespace are being ignored (or not ignored.) in the select.  Maybe there was an email in the middle that I missed

 

create table matt_test (c1 int)

 

insert into matt_test  values ('123')

 

insert into matt_test  values (' 123')

 

insert into matt_test values ('123 ')

 

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, that's totally expected behaviour. The "problem" is that it's pretty much obvious behaviour as well.

Your table is defined to store numbers not text.

So, when you do

     insert into matt_test  values ('123'); -- with any combination of leading/trailing spaces

Postgresql converts it to

    insert into matt_test values(123)

So, all three inserts you did are actually the same, and all store the *NUMBER* 123 in the table. Spaces are not part of the number, so are not stored

When you make the table store 'TEXT' or VARCHAR fields, then spaces ARE relevant for that type, so the data stored is different. For CHAR fields, they are space-padded or truncated as necessary to be the defined field size.

This is all pretty much basic SQL behaviour. Any correctly implemented SQL database server will behave exactly the same.

Paul

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
alternativewould 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