pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedThe same result for with SPACE and without SPACE
22+ messages / 8 participants
[nested] [flat]
* The same result for with SPACE and without SPACE
@ 2023-06-14 21:07 M Sarwar <sarwarmd02@outlook.com>
2023-06-14 21:19 ` Re: The same result for with SPACE and without SPACE David G. Johnston <david.g.johnston@gmail.com>
2023-06-14 21:51 ` The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
0 siblings, 2 replies; 22+ messages in thread
From: M Sarwar @ 2023-06-14 21:07 UTC (permalink / raw)
To: pgsql-advocacy@postgresql.org <pgsql-advocacy@postgresql.org>
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!
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: The same result for with SPACE and without SPACE
2023-06-14 21:07 The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
@ 2023-06-14 21:19 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 22+ messages in thread
From: David G. Johnston @ 2023-06-14 21:19 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; +Cc: pgsql-advocacy@postgresql.org <pgsql-advocacy@postgresql.org>
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 <sarwarmd02@outlook.com> 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.
^ permalink raw reply [nested|flat] 22+ messages in thread
* The same result for with SPACE and without SPACE
2023-06-14 21:07 The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
@ 2023-06-14 21:51 ` M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:18 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
1 sibling, 1 reply; 22+ messages in thread
From: M Sarwar @ 2023-06-14 21:51 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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 case, SQL-2 should have fetched no rows.
Does anyone not agree on this?
Thanks,
Sarwar
From Greenbelt in Maryland!
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: The same result for with SPACE and without SPACE
2023-06-14 21:07 The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 21:51 ` The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
@ 2023-06-14 22:18 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-14 22:31 ` Re: The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
0 siblings, 1 reply; 22+ messages in thread
From: Scott Ribe @ 2023-06-14 22:18 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Did you use type char instead of varchar?
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: The same result for with SPACE and without SPACE
2023-06-14 21:07 The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 21:51 ` The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:18 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
@ 2023-06-14 22:31 ` M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:45 ` Re: The same result for with SPACE and without SPACE Tom Lane <tgl@sss.pgh.pa.us>
2023-06-14 22:47 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-14 22:54 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 3 replies; 22+ messages in thread
From: M Sarwar @ 2023-06-14 22:31 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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 <scott_ribe@elevated-dev.com>
Sent: Wednesday, June 14, 2023 6:18 PM
To: M Sarwar <sarwarmd02@outlook.com>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: The same result for with SPACE and without SPACE
Did you use type char instead of varchar?
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: The same result for with SPACE and without SPACE
2023-06-14 21:07 The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 21:51 ` The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:18 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-14 22:31 ` Re: The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
@ 2023-06-14 22:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-06-15 13:08 ` The same result for with SPACE and without SPACE Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-06-15 17:13 ` Re: The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2 siblings, 2 replies; 22+ messages in thread
From: Tom Lane @ 2023-06-14 22:45 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; +Cc: Scott Ribe <scott_ribe@elevated-dev.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 22+ messages in thread
* The same result for with SPACE and without SPACE
2023-06-14 21:07 The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 21:51 ` The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:18 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-14 22:31 ` Re: The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:45 ` Re: The same result for with SPACE and without SPACE Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-06-15 13:08 ` Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-06-15 13:38 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
1 sibling, 1 reply; 22+ messages in thread
From: Wetmore, Matthew (CTR) @ 2023-06-15 13:08 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; M Sarwar <sarwarmd02@outlook.com>; +Cc: Scott Ribe <scott_ribe@elevated-dev.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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__;!!GFE8...
regards, tom lane
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: The same result for with SPACE and without SPACE
2023-06-14 21:07 The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 21:51 ` The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:18 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-14 22:31 ` Re: The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:45 ` Re: The same result for with SPACE and without SPACE Tom Lane <tgl@sss.pgh.pa.us>
2023-06-15 13:08 ` The same result for with SPACE and without SPACE Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
@ 2023-06-15 13:38 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-15 13:44 ` Re: The same result for with SPACE and without SPACE Mohammed Aslam <hmdaslam97@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Scott Ribe @ 2023-06-15 13:38 UTC (permalink / raw)
To: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; M Sarwar <sarwarmd02@outlook.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> 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 will be blank padded.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: The same result for with SPACE and without SPACE
2023-06-14 21:07 The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 21:51 ` The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:18 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-14 22:31 ` Re: The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:45 ` Re: The same result for with SPACE and without SPACE Tom Lane <tgl@sss.pgh.pa.us>
2023-06-15 13:08 ` The same result for with SPACE and without SPACE Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-06-15 13:38 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
@ 2023-06-15 13:44 ` Mohammed Aslam <hmdaslam97@gmail.com>
2023-06-15 14:11 ` Re: The same result for with SPACE and without SPACE mahesh <mahesha_dba@yahoo.com>
0 siblings, 1 reply; 22+ messages in thread
From: Mohammed Aslam @ 2023-06-15 13:44 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>; Tom Lane <tgl@sss.pgh.pa.us>; M Sarwar <sarwarmd02@outlook.com>; pgsql-admin@lists.postgresql.org
Kindly remove from here
On Thu, 15 Jun, 2023, 7:09 pm Scott Ribe, <scott_ribe@elevated-dev.com>
wrote:
> > 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 will be blank padded.
>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: The same result for with SPACE and without SPACE
2023-06-14 21:07 The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 21:51 ` The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:18 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-14 22:31 ` Re: The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:45 ` Re: The same result for with SPACE and without SPACE Tom Lane <tgl@sss.pgh.pa.us>
2023-06-15 13:08 ` The same result for with SPACE and without SPACE Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-06-15 13:38 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-15 13:44 ` Re: The same result for with SPACE and without SPACE Mohammed Aslam <hmdaslam97@gmail.com>
@ 2023-06-15 14:11 ` mahesh <mahesha_dba@yahoo.com>
2023-06-15 15:21 ` The same result for with SPACE and without SPACE Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
0 siblings, 1 reply; 22+ messages in thread
From: mahesh @ 2023-06-15 14:11 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; Mohammed Aslam <hmdaslam97@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; +Cc: Wetmore, Matthew (CTR) <matthew.wetmore@express-scripts.com>; Tom Lane <tgl@sss.pgh.pa.us>; M Sarwar <sarwarmd02@outlook.com>
Kindly remove from from the group..
On Thursday, June 15, 2023 at 08:44:58 AM CDT, Mohammed Aslam <hmdaslam97@gmail.com> wrote:
Kindly remove from here
On Thu, 15 Jun, 2023, 7:09 pm Scott Ribe, <scott_ribe@elevated-dev.com> wrote:
> 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 will be blank padded.
^ permalink raw reply [nested|flat] 22+ messages in thread
* The same result for with SPACE and without SPACE
2023-06-14 21:07 The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 21:51 ` The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:18 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-14 22:31 ` Re: The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:45 ` Re: The same result for with SPACE and without SPACE Tom Lane <tgl@sss.pgh.pa.us>
2023-06-15 13:08 ` The same result for with SPACE and without SPACE Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-06-15 13:38 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-15 13:44 ` Re: The same result for with SPACE and without SPACE Mohammed Aslam <hmdaslam97@gmail.com>
2023-06-15 14:11 ` Re: The same result for with SPACE and without SPACE mahesh <mahesha_dba@yahoo.com>
@ 2023-06-15 15:21 ` Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-06-15 15:48 ` Re: The same result for with SPACE and without SPACE Paul Smith* <paul@pscs.co.uk>
2023-06-15 15:53 ` Re: The same result for with SPACE and without SPACE David G. Johnston <david.g.johnston@gmail.com>
2023-06-15 15:56 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 3 replies; 22+ messages in thread
From: Wetmore, Matthew (CTR) @ 2023-06-15 15:21 UTC (permalink / raw)
To: mahesh <mahesha_dba@yahoo.com>; Scott Ribe <scott_ribe@elevated-dev.com>; Mohammed Aslam <hmdaslam97@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; M Sarwar <sarwarmd02@outlook.com>
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 <mahesha_dba@yahoo.com>
Sent: Thursday, June 15, 2023 7:12 AM
To: Scott Ribe <scott_ribe@elevated-dev.com>; Mohammed Aslam <hmdaslam97@gmail.com>; pgsql-admin@lists.postgresql.org
Cc: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>; Tom Lane <tgl@sss.pgh.pa.us>; M Sarwar <sarwarmd02@outlook.com>
Subject: [EXTERNAL] Re: The same result for with SPACE and without SPACE
Kindly remove from from the group..
On Thursday, June 15, 2023 at 08:44:58 AM CDT, Mohammed Aslam <hmdaslam97@gmail.com<mailto:hmdaslam97@gmail.com>> wrote:
Kindly remove from here
On Thu, 15 Jun, 2023, 7:09 pm Scott Ribe, <scott_ribe@elevated-dev.com<mailto:scott_ribe@elevated-dev.com>> wrote:
> On Jun 15, 2023, at 7:08 AM, Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com<mailto: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 will be blank padded.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: The same result for with SPACE and without SPACE
2023-06-14 21:07 The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 21:51 ` The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:18 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-14 22:31 ` Re: The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:45 ` Re: The same result for with SPACE and without SPACE Tom Lane <tgl@sss.pgh.pa.us>
2023-06-15 13:08 ` The same result for with SPACE and without SPACE Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-06-15 13:38 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-15 13:44 ` Re: The same result for with SPACE and without SPACE Mohammed Aslam <hmdaslam97@gmail.com>
2023-06-15 14:11 ` Re: The same result for with SPACE and without SPACE mahesh <mahesha_dba@yahoo.com>
2023-06-15 15:21 ` The same result for with SPACE and without SPACE Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
@ 2023-06-15 15:48 ` Paul Smith* <paul@pscs.co.uk>
2023-06-15 17:26 ` Re: The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2 siblings, 1 reply; 22+ messages in thread
From: Paul Smith* @ 2023-06-15 15:48 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 15/06/2023 16:21, Wetmore, Matthew (CTR) wrote:
>
> 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
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: The same result for with SPACE and without SPACE
2023-06-14 21:07 The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 21:51 ` The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:18 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-14 22:31 ` Re: The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:45 ` Re: The same result for with SPACE and without SPACE Tom Lane <tgl@sss.pgh.pa.us>
2023-06-15 13:08 ` The same result for with SPACE and without SPACE Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-06-15 13:38 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-15 13:44 ` Re: The same result for with SPACE and without SPACE Mohammed Aslam <hmdaslam97@gmail.com>
2023-06-15 14:11 ` Re: The same result for with SPACE and without SPACE mahesh <mahesha_dba@yahoo.com>
2023-06-15 15:21 ` The same result for with SPACE and without SPACE Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-06-15 15:48 ` Re: The same result for with SPACE and without SPACE Paul Smith* <paul@pscs.co.uk>
@ 2023-06-15 17:26 ` M Sarwar <sarwarmd02@outlook.com>
0 siblings, 0 replies; 22+ messages in thread
From: M Sarwar @ 2023-06-15 17:26 UTC (permalink / raw)
To: Paul Smith* <paul@pscs.co.uk>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
I guess behaviour is the same in Oracle as well.
Thanks,
Sarwar
________________________________
From: Paul Smith* <paul@pscs.co.uk>
Sent: Thursday, June 15, 2023 11:48 AM
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: The same result for with SPACE and without SPACE
On 15/06/2023 16:21, Wetmore, Matthew (CTR) wrote:
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
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: The same result for with SPACE and without SPACE
2023-06-14 21:07 The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 21:51 ` The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:18 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-14 22:31 ` Re: The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:45 ` Re: The same result for with SPACE and without SPACE Tom Lane <tgl@sss.pgh.pa.us>
2023-06-15 13:08 ` The same result for with SPACE and without SPACE Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-06-15 13:38 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-15 13:44 ` Re: The same result for with SPACE and without SPACE Mohammed Aslam <hmdaslam97@gmail.com>
2023-06-15 14:11 ` Re: The same result for with SPACE and without SPACE mahesh <mahesha_dba@yahoo.com>
2023-06-15 15:21 ` The same result for with SPACE and without SPACE Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
@ 2023-06-15 15:53 ` David G. Johnston <david.g.johnston@gmail.com>
2 siblings, 0 replies; 22+ messages in thread
From: David G. Johnston @ 2023-06-15 15:53 UTC (permalink / raw)
To: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>; +Cc: mahesh <mahesha_dba@yahoo.com>; Scott Ribe <scott_ribe@elevated-dev.com>; Mohammed Aslam <hmdaslam97@gmail.com>; pgsql-admin@lists.postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>; M Sarwar <sarwarmd02@outlook.com>
On Thu, Jun 15, 2023, 08:22 Wetmore, Matthew (CTR) <
Matthew.Wetmore@express-scripts.com> wrote:
> 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.
>
>
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.
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: The same result for with SPACE and without SPACE
2023-06-14 21:07 The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 21:51 ` The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:18 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-14 22:31 ` Re: The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:45 ` Re: The same result for with SPACE and without SPACE Tom Lane <tgl@sss.pgh.pa.us>
2023-06-15 13:08 ` The same result for with SPACE and without SPACE Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-06-15 13:38 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-15 13:44 ` Re: The same result for with SPACE and without SPACE Mohammed Aslam <hmdaslam97@gmail.com>
2023-06-15 14:11 ` Re: The same result for with SPACE and without SPACE mahesh <mahesha_dba@yahoo.com>
2023-06-15 15:21 ` The same result for with SPACE and without SPACE Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
@ 2023-06-15 15:56 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-15 17:43 ` Re: The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2 siblings, 1 reply; 22+ messages in thread
From: Scott Ribe @ 2023-06-15 15:56 UTC (permalink / raw)
To: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>; +Cc: mahesh <mahesha_dba@yahoo.com>; Mohammed Aslam <hmdaslam97@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; Tom Lane <tgl@sss.pgh.pa.us>; M Sarwar <sarwarmd02@outlook.com>
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
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: The same result for with SPACE and without SPACE
2023-06-14 21:07 The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 21:51 ` The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:18 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-14 22:31 ` Re: The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:45 ` Re: The same result for with SPACE and without SPACE Tom Lane <tgl@sss.pgh.pa.us>
2023-06-15 13:08 ` The same result for with SPACE and without SPACE Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-06-15 13:38 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-15 13:44 ` Re: The same result for with SPACE and without SPACE Mohammed Aslam <hmdaslam97@gmail.com>
2023-06-15 14:11 ` Re: The same result for with SPACE and without SPACE mahesh <mahesha_dba@yahoo.com>
2023-06-15 15:21 ` The same result for with SPACE and without SPACE Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-06-15 15:56 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
@ 2023-06-15 17:43 ` M Sarwar <sarwarmd02@outlook.com>
2023-06-15 18:04 ` Re: The same result for with SPACE and without SPACE David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: M Sarwar @ 2023-06-15 17:43 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>; +Cc: mahesh <mahesha_dba@yahoo.com>; Mohammed Aslam <hmdaslam97@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; Tom Lane <tgl@sss.pgh.pa.us>
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 <scott_ribe@elevated-dev.com>
Sent: Thursday, June 15, 2023 11:56 AM
To: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
Cc: mahesh <mahesha_dba@yahoo.com>; Mohammed Aslam <hmdaslam97@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; Tom Lane <tgl@sss.pgh.pa.us>; M Sarwar <sarwarmd02@outlook.com>
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
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: The same result for with SPACE and without SPACE
2023-06-14 21:07 The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 21:51 ` The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:18 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-14 22:31 ` Re: The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:45 ` Re: The same result for with SPACE and without SPACE Tom Lane <tgl@sss.pgh.pa.us>
2023-06-15 13:08 ` The same result for with SPACE and without SPACE Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-06-15 13:38 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-15 13:44 ` Re: The same result for with SPACE and without SPACE Mohammed Aslam <hmdaslam97@gmail.com>
2023-06-15 14:11 ` Re: The same result for with SPACE and without SPACE mahesh <mahesha_dba@yahoo.com>
2023-06-15 15:21 ` The same result for with SPACE and without SPACE Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-06-15 15:56 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-15 17:43 ` Re: The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
@ 2023-06-15 18:04 ` David G. Johnston <david.g.johnston@gmail.com>
2023-06-15 22:50 ` Re: The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
0 siblings, 1 reply; 22+ messages in thread
From: David G. Johnston @ 2023-06-15 18:04 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; +Cc: Scott Ribe <scott_ribe@elevated-dev.com>; Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>; mahesh <mahesha_dba@yahoo.com>; Mohammed Aslam <hmdaslam97@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; Tom Lane <tgl@sss.pgh.pa.us>
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 char*10 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.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: The same result for with SPACE and without SPACE
2023-06-14 21:07 The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 21:51 ` The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:18 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-14 22:31 ` Re: The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:45 ` Re: The same result for with SPACE and without SPACE Tom Lane <tgl@sss.pgh.pa.us>
2023-06-15 13:08 ` The same result for with SPACE and without SPACE Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-06-15 13:38 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-15 13:44 ` Re: The same result for with SPACE and without SPACE Mohammed Aslam <hmdaslam97@gmail.com>
2023-06-15 14:11 ` Re: The same result for with SPACE and without SPACE mahesh <mahesha_dba@yahoo.com>
2023-06-15 15:21 ` The same result for with SPACE and without SPACE Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-06-15 15:56 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-15 17:43 ` Re: The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-15 18:04 ` Re: The same result for with SPACE and without SPACE David G. Johnston <david.g.johnston@gmail.com>
@ 2023-06-15 22:50 ` M Sarwar <sarwarmd02@outlook.com>
0 siblings, 0 replies; 22+ messages in thread
From: M Sarwar @ 2023-06-15 22:50 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Scott Ribe <scott_ribe@elevated-dev.com>; Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>; mahesh <mahesha_dba@yahoo.com>; Mohammed Aslam <hmdaslam97@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; Tom Lane <tgl@sss.pgh.pa.us>
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.g.johnston@gmail.com>
Sent: Thursday, June 15, 2023 2:04 PM
To: M Sarwar <sarwarmd02@outlook.com>
Cc: Scott Ribe <scott_ribe@elevated-dev.com>; Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>; mahesh <mahesha_dba@yahoo.com>; Mohammed Aslam <hmdaslam97@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: The same result for with SPACE and without SPACE
On Thu, Jun 15, 2023 at 10:44 AM M Sarwar <sarwarmd02@outlook.com<mailto: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.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: The same result for with SPACE and without SPACE
2023-06-14 21:07 The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 21:51 ` The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:18 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-14 22:31 ` Re: The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:45 ` Re: The same result for with SPACE and without SPACE Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-06-15 17:13 ` M Sarwar <sarwarmd02@outlook.com>
1 sibling, 0 replies; 22+ messages in thread
From: M Sarwar @ 2023-06-15 17:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
@Tom Lane<mailto:tgl@sss.pgh.pa.us>
Thanks for this email.
I am checking all the emails.
Sincerely,
Sarwar
________________________________
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Wednesday, June 14, 2023 6:45 PM
To: M Sarwar <sarwarmd02@outlook.com>
Cc: Scott Ribe <scott_ribe@elevated-dev.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: 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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fdocs%2Fcurrent...;
regards, tom lane
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: The same result for with SPACE and without SPACE
2023-06-14 21:07 The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 21:51 ` The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:18 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-14 22:31 ` Re: The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
@ 2023-06-14 22:47 ` Scott Ribe <scott_ribe@elevated-dev.com>
2 siblings, 0 replies; 22+ messages in thread
From: Scott Ribe @ 2023-06-14 22:47 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> 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 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.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: The same result for with SPACE and without SPACE
2023-06-14 21:07 The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 21:51 ` The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:18 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-14 22:31 ` Re: The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
@ 2023-06-14 22:54 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-15 17:16 ` Re: The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2 siblings, 1 reply; 22+ messages in thread
From: Scott Ribe @ 2023-06-14 22:54 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: The same result for with SPACE and without SPACE
2023-06-14 21:07 The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 21:51 ` The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:18 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-14 22:31 ` Re: The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:54 ` Re: The same result for with SPACE and without SPACE Scott Ribe <scott_ribe@elevated-dev.com>
@ 2023-06-15 17:16 ` M Sarwar <sarwarmd02@outlook.com>
0 siblings, 0 replies; 22+ messages in thread
From: M Sarwar @ 2023-06-15 17:16 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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,
________________________________
From: Scott Ribe <scott_ribe@elevated-dev.com>
Sent: Wednesday, June 14, 2023 6:54 PM
To: M Sarwar <sarwarmd02@outlook.com>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: The same result for with SPACE and without SPACE
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.
^ permalink raw reply [nested|flat] 22+ messages in thread
end of thread, other threads:[~2023-06-15 22:50 UTC | newest]
Thread overview: 22+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-06-14 21:07 The same result for with SPACE and without SPACE M Sarwar <sarwarmd02@outlook.com>
2023-06-14 21:19 ` David G. Johnston <david.g.johnston@gmail.com>
2023-06-14 21:51 ` M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:18 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-14 22:31 ` M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-06-15 13:08 ` Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-06-15 13:38 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-15 13:44 ` Mohammed Aslam <hmdaslam97@gmail.com>
2023-06-15 14:11 ` mahesh <mahesha_dba@yahoo.com>
2023-06-15 15:21 ` Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-06-15 15:48 ` Paul Smith* <paul@pscs.co.uk>
2023-06-15 17:26 ` M Sarwar <sarwarmd02@outlook.com>
2023-06-15 15:53 ` David G. Johnston <david.g.johnston@gmail.com>
2023-06-15 15:56 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-15 17:43 ` M Sarwar <sarwarmd02@outlook.com>
2023-06-15 18:04 ` David G. Johnston <david.g.johnston@gmail.com>
2023-06-15 22:50 ` M Sarwar <sarwarmd02@outlook.com>
2023-06-15 17:13 ` M Sarwar <sarwarmd02@outlook.com>
2023-06-14 22:47 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-14 22:54 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-15 17:16 ` M Sarwar <sarwarmd02@outlook.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox