Subject: The same result for with SPACE and without SPACE

From: M Sarwar <[email-masked]>
Date: Wed, 14 Jun 2023 21:07:50 +0000

Postgres: PostgreSQL 13.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit
Cloud: AWS RDS
pgAdmin: 4

SQL-1:-     with SPACE in WHERE clause
select * from  BRONX.TEST_TEST_DETAILS_ALL_MCM where test_number = 'TEST4P20 ';

Result-1:
747   "AB                                                "  "TEST4P20  "      " Max "     " IIH 1.8V Max "  "uA                  "  -405.000000 405.000000  "2023-06-14 01:12:17.714181+00"     
836   "AB                                                "  "TEST4P20  "      "Max" "IIH 1.8V Max"    "uA                  "  -405.000000 405.000000  "2023-06-14 01:13:17.869917+00"     
906   "BI                                                "  "TEST4P20  "      "PS16 Current (V7)"     "2.2V I DUT1-4"   "A                   "  0.001000    100.000000  "2023-06-14 01:14:04.95952+00"      

SQL-2:- without SPACE in WHERE clause
select * from  BRONX.TEST_TEST_DETAILS_ALL_MCM where test_number = 'TEST4P20';

Result-2:
747   "AB                                                "  "TEST4P20  "      " Max "     " IIH 1.8V Max "  "uA                  "  -405.000000 405.000000  "2023-06-14 01:12:17.714181+00"           "AMB"
836   "AB                                                "  "TEST4P20  "      "Max" "IIH 1.8V Max"    "uA                  "  -405.000000 405.000000  "2023-06-14 01:13:17.869917+00"           "HOT"
906   "BI                                                "  "TEST4P20  "      "PS16 Current (V7)"     "2.2V I DUT1-4"   "A                   "  0.001000    100.000000  "2023-06-14 01:14:04.95952+00"        

Please note in SQL-1 that test_number = 'TEST4P20 ' has a space and in SQL-2 there is no space test_number = 'TEST4P20' has no space.

Is it not a bug?
I do not want the above queries to fetch the same results.
I check the data closely which does has a SPACE in the table. In which SQL-2 should have fetched no rows.
Does anyone not agree on this?

Thanks,
Sarwar
From Greenbelt in Maryland!

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 14 Jun 2023 14:19:53 -0700

As noted on this page, the -general list is a much more appropriate place
to send this question.  Advocacy, not even close.
https://www.postgresql.org/list/

On Wed, Jun 14, 2023 at 2:08 PM M Sarwar <[email-masked]> wrote:

>
> Please note in SQL-1 that test_number = 'TEST4P20 ' has a space and in
> SQL-2 there is no space test_number = 'TEST4P20' has no space.
>
> Is it not a bug?
>

Doubtful.  You haven't provided actual data types here but it seems you are
using character instead of text.  Your desired behavior is provided by the
text data type.

David J.

---

From: M Sarwar <[email-masked]>
Date: Wed, 14 Jun 2023 21:51:06 +0000

Result-1:

Result-2:

Is it not a bug?
I do not want the above queries to fetch the same results.
I check the data closely which does has a SPACE in the table. In which case, SQL-2 should have fetched no rows.
Does anyone not agree on this?

Thanks,
Sarwar

---

From: Scott Ribe <[email-masked]>
Date: Wed, 14 Jun 2023 16:18:11 -0600

Did you use type char instead of varchar?

---

From: M Sarwar <[email-masked]>
Date: Wed, 14 Jun 2023 22:31:34 +0000

Scott,

This is coming from CHAR(10).

test_number character(10) COLLATE pg_catalog."default",

I expect this to behave the same way whether it comes from CHAR or VARCHAR.
Does that matter?
Thanks,
Sarwar

________________________________
From: Scott Ribe <[email-masked]>
Sent: Wednesday, June 14, 2023 6:18 PM
To: M Sarwar <[email-masked]>
Cc: [email-masked] <[email-masked]>
Subject: Re: The same result for with SPACE and without SPACE

---

From: Tom Lane <[email-masked]>
Date: Wed, 14 Jun 2023 18:45:39 -0400

> 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

---

From: Scott Ribe <[email-masked]>
Date: Wed, 14 Jun 2023 16:47:21 -0600

> On Jun 14, 2023, at 4:31 PM, M Sarwar <[email-masked]> wrote:
> 

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 requires padding 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* value will be space-padded to 10.

---

From: Scott Ribe <[email-masked]>
Date: Wed, 14 Jun 2023 16:54:50 -0600

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

---

From: "Wetmore, Matthew (CTR)" <[email-masked]>
Date: Thu, 15 Jun 2023 13:08:06 +0000

I can reproduce this with a INT

-----Original Message-----
From: Tom Lane <[email-masked]> 
Sent: Wednesday, June 14, 2023 3:46 PM
To: M Sarwar <[email-masked]>
Cc: Scott Ribe <[email-masked]>; [email-masked]
Subject: [EXTERNAL] Re: The same result for with SPACE and without SPACE

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

			regards, tom lane

---

From: Scott Ribe <[email-masked]>
Date: Thu, 15 Jun 2023 07:38:36 -0600

> On Jun 15, 2023, at 7:08 AM, Wetmore, Matthew (CTR) <[email-masked]> wrote:
> 

?

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

---

From: Mohammed Aslam <[email-masked]>
Date: Thu, 15 Jun 2023 19:14:33 +0530

Kindly remove from here

On Thu, 15 Jun, 2023, 7:09 pm Scott Ribe, <[email-masked]>
wrote:

> > On Jun 15, 2023, at 7:08 AM, Wetmore, Matthew (CTR) <
> [email-masked]> wrote:
> >
> When casting string to integer, trailing spaces do not cause an exception.
> When casting integer to char(n), the string will be blank padded.
>
>

---

From: mahesh <[email-masked]>
Date: Thu, 15 Jun 2023 14:11:33 +0000 (UTC)

Kindly remove from from the group..

    On Thursday, June 15, 2023 at 08:44:58 AM CDT, Mohammed Aslam <[email-masked]> wrote:  
 
 Kindly remove from here

?

---

From: "Wetmore, Matthew (CTR)" <[email-masked]>
Date: Thu, 15 Jun 2023 15:21:56 +0000

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

Works as I would expect with TEXT

create table matt_test2 (c2 text)

insert into matt_test2  values ('123')

insert into matt_test2  values (' 123')

insert into matt_test2  values ('123 ')

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

From: mahesh <[email-masked]>
Sent: Thursday, June 15, 2023 7:12 AM
To: Scott Ribe <[email-masked]>; Mohammed Aslam <[email-masked]>; [email-masked]
Cc: Wetmore, Matthew (CTR) <[email-masked]>; Tom Lane <[email-masked]>; M Sarwar <[email-masked]>
Subject: [EXTERNAL] Re: The same result for with SPACE and without SPACE

On Thursday, June 15, 2023 at 08:44:58 AM CDT, Mohammed Aslam <[email-masked]<mailto:[email-masked]>> wrote:

Kindly remove from here

On Thu, 15 Jun, 2023, 7:09 pm Scott Ribe, <[email-masked]<mailto:[email-masked]>> wrote:
> On Jun 15, 2023, at 7:08 AM, Wetmore, Matthew (CTR) <[email-masked]<mailto:[email-masked]>> wrote:
>

?

---

From: Paul Smith* <[email-masked]>
Date: Thu, 15 Jun 2023 16:48:18 +0100

On 15/06/2023 16:21, Wetmore, Matthew (CTR) wrote:
> 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
>
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

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 15 Jun 2023 08:53:20 -0700

On Thu, Jun 15, 2023, 08:22 Wetmore, Matthew (CTR) <
[email-masked]> wrote:

> 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.
>
>
>
If anything the inserts themselves should fail in trying to parse the
string to integers.  But that works just fine so all three rows have the
same integer output for the different inputs.

David J.

---

From: Scott Ribe <[email-masked]>
Date: Thu, 15 Jun 2023 09:56:37 -0600

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.

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

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

---

From: M Sarwar <[email-masked]>
Date: Thu, 15 Jun 2023 17:13:10 +0000

@Tom Lane<mailto:[email-masked]>
Thanks for this email.
I am checking all the emails.
Sincerely,
Sarwar

________________________________
From: Tom Lane <[email-masked]>
Sent: Wednesday, June 14, 2023 6:45 PM
To: M Sarwar <[email-masked]>
Cc: Scott Ribe <[email-masked]>; [email-masked] <[email-masked]>
Subject: Re: The same result for with SPACE and without SPACE

https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fdocs%2Fcurrent%2Fdatatype-character.html&data=05%7C01%7C%7C10a23b4db34a484aa3cb08db6d291b28%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638223795537107272%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=PMlQtWXS2OrzG%2FNZlFB3qGpKwKaTjnA4fsEQKFTVivk%3D&reserved=0<https://www.postgresql.org/docs/current/datatype-character.html>

                        regards, tom lane

---

From: M Sarwar <[email-masked]>
Date: Thu, 15 Jun 2023 17:16:53 +0000

Yes, This is what I am understanding.
I am in the database administration for couple of decades but never realized this intricacies.
Probably this is more known in the development side. I am learning now 🙂
Thank you,

Sent: Wednesday, June 14, 2023 6:54 PM

---

From: M Sarwar <[email-masked]>
Date: Thu, 15 Jun 2023 17:26:28 +0000

I guess behaviour is the same in Oracle as well.
Thanks,
Sarwar

________________________________
From: Paul Smith* <[email-masked]>
Sent: Thursday, June 15, 2023 11:48 AM
To: [email-masked] <[email-masked]>
Subject: Re: The same result for with SPACE and without SPACE

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

-- all 3 rows returned.

Yes, that's totally expected behaviour. The "problem" is that it's pretty much obvious behaviour as well.

So, when you do

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

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

---

From: M Sarwar <[email-masked]>
Date: Thu, 15 Jun 2023 17:43:51 +0000

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?

Thanks,
Sarwar

________________________________
From: Scott Ribe <[email-masked]>
Sent: Thursday, June 15, 2023 11:56 AM
To: Wetmore, Matthew (CTR) <[email-masked]>
Cc: mahesh <[email-masked]>; Mohammed Aslam <[email-masked]>; [email-masked] <[email-masked]>; Tom Lane <[email-masked]>; M Sarwar <[email-masked]>
Subject: Re: The same result for with SPACE and without SPACE

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 15 Jun 2023 11:04:41 -0700

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

> Now I compare char*10 and var_char_10 columns, I will get the wrong
> results because char*_10 has padded spaces.
>
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: M Sarwar <[email-masked]>
Date: Thu, 15 Jun 2023 22:50:12 +0000

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 <[email-masked]>
Sent: Thursday, June 15, 2023 2:04 PM
To: M Sarwar <[email-masked]>
Cc: Scott Ribe <[email-masked]>; Wetmore, Matthew (CTR) <[email-masked]>; mahesh <[email-masked]>; Mohammed Aslam <[email-masked]>; [email-masked] <[email-masked]>; Tom Lane <[email-masked]>
Subject: Re: The same result for with SPACE and without SPACE

On Thu, Jun 15, 2023 at 10:44 AM M Sarwar <[email-masked]<mailto:[email-masked]>> wrote:
Hello Scott and all,

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.

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

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.