Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q9qk0-0006rU-FP for pgsql-admin@arkaria.postgresql.org; Thu, 15 Jun 2023 17:26:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q9qjz-00085H-DZ for pgsql-admin@arkaria.postgresql.org; Thu, 15 Jun 2023 17:26:35 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q9qjy-000855-Di for pgsql-admin@lists.postgresql.org; Thu, 15 Jun 2023 17:26:35 +0000 Received: from mail-bn1nam02olkn2075.outbound.protection.outlook.com ([40.92.15.75] helo=NAM02-BN1-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1q9qjv-002O0V-6O for pgsql-admin@lists.postgresql.org; Thu, 15 Jun 2023 17:26:32 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=Xwc9k35SfU2G+2V+Scbfkq6ss07BP2kVwG6yr2uGhE26di53tggdGQaWLeWWTrQHUmGdCqSCKMajoJ4BZaLoqewQHU8XxABrWGoC9bPYkXnxpnNJ6VA/5Q3+ZBU1ehein7mPD2dmvvROHr862qr+3rDVaFLzYDVpDXswb/9ScSAEraZ2o8mWprpC30rzymDcXxYlFMInBfBIq8+tSXy13Nflh4uHAs1/YeNvcfTKcdVFN9WhpGuYjtwEoW6yhVPfeoO9OfgoRQknu8dWRjDCrWD081NFWQWlgKXHah8al0AFwf6l+u0BWGLWiDGZ9hkMRJMOg3nY4L4cBXsJasmRrg== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=7Gwyi+g6uz1lqpij07WWh8OuHgAeDI0rS80mME0K6vc=; b=bRHu6L49LhmO5wHZ5duRcTBI55CIqJFf6B6uClLed3ZugivQVZFJblzJvj0q1D+BkGgGC0Sd5ImQ8LbD6nwhU2Da+m1tocmTsDR6FSI/aTatrQbvAUjTI/0oWx3ZabDh86b1ba1dn1usipHY4oZneRCNF4d6yQi9dKOlF0v6t13sXz329lqR94A4Z0P1rARSdcbyZ/8GjL67LS5fJURnfNS371iOfjoH0lDIQ9kNnVhXEnX7sr09Xs6wzcZbxrSXjdBWUjobAK5+/DL7f+A4aMXesHIE5Nt42dJ41/KKRN9lLwJimjG4RqWci9MaI5ztr8sJPQ9CE3fXXfHfx5RFMA== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=none; dmarc=none; dkim=none; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=outlook.com; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=7Gwyi+g6uz1lqpij07WWh8OuHgAeDI0rS80mME0K6vc=; b=NViJO4OcwaeUl8rrZnSx1LO59VuaRCDAMqfnt+TQaGMyDJOsMBXBC8km1PELFyghQS+7huKOSY1S5OAOnTjzzoPvWBHgj3oIkmIpgb31JFd9EFDPNtgbMr2cRyRBx6z4D+Zb0Raq3Su+T/ZMSDEwBhMZ2vOadlRuDtvlLnsDQ4UMD/GRouG6zPtYxyRIGAk78gIceh2z/5wH8cNxpDd/GTmi6xKoLlUDFjYStbvTRDFJCCfKHRmyByiakmx9w+EHXelOUleAgjjB9+7vPyp3kHXrYLnkM9fBO3bldblfqfuvBwbAyrLchJ2BOydubLfaynNRBd7hhTXfBN6Dikhajg== Received: from DM4PR19MB5978.namprd19.prod.outlook.com (2603:10b6:8:6a::18) by MN0PR19MB6141.namprd19.prod.outlook.com (2603:10b6:208:3cf::8) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.6500.25; Thu, 15 Jun 2023 17:26:28 +0000 Received: from DM4PR19MB5978.namprd19.prod.outlook.com ([fe80::47d8:cd2d:fe7b:ac1a]) by DM4PR19MB5978.namprd19.prod.outlook.com ([fe80::47d8:cd2d:fe7b:ac1a%3]) with mapi id 15.20.6500.026; Thu, 15 Jun 2023 17:26:28 +0000 From: M Sarwar To: Paul Smith* , "pgsql-admin@lists.postgresql.org" Subject: Re: The same result for with SPACE and without SPACE Thread-Topic: The same result for with SPACE and without SPACE Thread-Index: AQHZnwJ9vgb1T+k9ckKN/se5g26vCa+K1kAmgAAH8ICAAANa0IAABFKAgADw+ACAAAiFAIAAAaqAgAAHi4CAABOqAIAAB14AgAAbNDQ= Date: Thu, 15 Jun 2023 17:26:28 +0000 Message-ID: References: <2018059.1686782739@sss.pgh.pa.us> <0055148e2cc742cbbc23485ae0c6c04e@express-scripts.com> <2CAFD37D-68FC-4A01-BE65-FAF1ADF7A60A@elevated-dev.com> <1726236757.407336.1686838293703@mail.yahoo.com> <3f21240f59a54f96898886f2c9189cf3@express-scripts.com> In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: msip_labels: x-ms-exchange-messagesentrepresentingtype: 1 x-tmn: [OYEV/Wy3cJmFywdkey/ndx4st0IrqtK5ziTXMFZJe9zux2XbDmKuNw==] x-ms-publictraffictype: Email x-ms-traffictypediagnostic: DM4PR19MB5978:EE_|MN0PR19MB6141:EE_ x-ms-office365-filtering-correlation-id: 8f8eef8d-18ba-4b2d-d200-08db6dc5a72b x-microsoft-antispam: BCL:0; x-microsoft-antispam-message-info: pqye9E53qA3vB9xRgFlb5qG38ScwH7M/Dt8+fhzG4wHmUJN4V9Ix5g1yKgcR6+tvwy+N+YUygVU25A2pWqhPArxQm2XsJl0QA3acLFCK7qjqPXO7gO4Y5T60SZ1o5dcz9w8oObcuYgI8qlGgCVNAnWAawtSHk4OR+lR6uniWIGDmrh63A/V2utnknbKPH3NgfOFDa4YVHDnmeu713NjUU/Se1je0GjA5Iyfnai2LA7+rNAMMEHwbAObz4poJWeOlfrSD7wCeBkh3JdkTxNdLS6GYSLIkiYSHk/TtTi2B9QF3P/7UItGmK3NtaSkysOpH3BI+Wa9y7qGvm7ULXuV7ZvTrKrsux5k/pMjDy5Oe5paqXLJIADSRKhX+Lcl8tH0zacJoRB+bLFtR2k7dolS2U4KuGxq1LfLdjfMjIvxVX1PU9r3rqxrO/P5bnjrnGAiHFHyqdOC1ag5QR+gXdX4u6r5jr3A693Bm6sToRuw3XNO4od3HPmTgcRF4vdeu7FDma9i5WE8z7ptAzMPy8YHK/NFkA1GFT07wZAiA4VtGfMfe4g6BK8uOZ0mkY0eoh2INfI/RhafVbfS5Y0dvyuDNdzbp9LOfhhv/KuJZwSBeOp6wqakLRo3yFtrbmz9yLgcr x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?Windows-1252?Q?AmB4t17wy7bYs06Xwrj58xeHIArOMs7Olg66yGZ76ftxyu3ldFAh4dYf?= =?Windows-1252?Q?IfOg+HlYTx7cs/ObrTNtrghX4HxdmPLHnjzs682ef45QLXCRr35Ljgjc?= =?Windows-1252?Q?9BJERXhvRCouUARnIHTtosIk+yhQr5XN+isXMjfN/85XNuRGMjP5P/H5?= =?Windows-1252?Q?6hRU82nshmLkXh2Znep1wCmqlduOSIoT7gkydzyeYSw+6BQclh1HVcqu?= =?Windows-1252?Q?McNqrkUpLGsD8BEOFsla/gLO3nwHXNqgtP+hthhp6DZT54Dn5mceSs6w?= =?Windows-1252?Q?QOIZVO4/rQq+ESacSHO80vVCuJybRnWjWyFsaRV3Lv2JoslJ54S+Ll9y?= =?Windows-1252?Q?I4HaYAuVuIqfVdSDQkAMlr9fmkPyjJNkLvzv67wOr7B2+slvxvg/hZlq?= =?Windows-1252?Q?C9Anrr5z49WgVEmQU1X1V41rY3xaVYcQOkzPFY8umhQh0VaLnFGPTrrV?= =?Windows-1252?Q?E3TfYVYMIxfEc4c+aGxJTapWqlEVKMmKzruZf19QJmalJAvVojnzdHCJ?= =?Windows-1252?Q?tULi08w59z/aGSQdBRyoUNmjyXK0o0BK7p5NAReSNtWt32XJ37AFDL1R?= =?Windows-1252?Q?FfVUErNK9rHjyu/maajs52dY9vu8fTuVYaRpFJlxnerlrslnt+BWpHlY?= =?Windows-1252?Q?UQ3luWrbnYDPZ3dJhG7hTnm+goJrHIDchSO/e6wYOEqPIX5IoVnklJx1?= =?Windows-1252?Q?ZLjxHn9g0U+8JiTGDW1VUJA+Jwl5EOQke7a0/+4djxdDHkXHZvfxi2H3?= =?Windows-1252?Q?nk9ceTarXdUjDn7NU2nhzCIM070X1qksoTbnPGVEJ+X/yU6+DSb3A2Pj?= =?Windows-1252?Q?IvxXqcz/Dw5kluLDwTkDIvGsD5rKdhz0z0TrCyqo2/08NxAXfTiw1a9B?= =?Windows-1252?Q?F0mv19f91kZY/uOdNv8nm23Vs4BMgRVnpZp7mNQ3Svx16fSPJ3v93MVO?= =?Windows-1252?Q?tHz62tBO/MZ56I5jXPtNwEyiIGWDl8gPmNO3wDsEy+1DVNTUUzpBIGoK?= =?Windows-1252?Q?YJduCTOEHQZhBNhA1eLXF5OtAonZ3H64t1ELTN3KaJAY72maO6WEHXHq?= =?Windows-1252?Q?U0DRPPyaVzbrUzdYfjLYe5YZ4i1rRxhhHLT3I9TKW5oC+jXjyrEGLfay?= =?Windows-1252?Q?RcdSzSn7G2Mnt5MHGBVRR81ILYg3hO8wz1zm1bi537hHUHQk60vWsOX8?= =?Windows-1252?Q?Vb7ycLAgQvr+zzxMJ44rhSpUado19j6JUKK1ZUHBF/UpUEN8mUs9Q7Dg?= =?Windows-1252?Q?lqwl6QYYWBaUA+J2rVz2yxck2qIe6X0Uw1TrNvUhTNMmeuNUdfjoRn1p?= =?Windows-1252?Q?RuI9CIumq3LtFZ3bKERbBrOeMmo=3D?= Content-Type: multipart/alternative; boundary="_000_DM4PR19MB597818A20E53C97BDB03AE8BD35BADM4PR19MB5978namp_" MIME-Version: 1.0 X-OriginatorOrg: outlook.com X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: DM4PR19MB5978.namprd19.prod.outlook.com X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-CrossTenant-Network-Message-Id: 8f8eef8d-18ba-4b2d-d200-08db6dc5a72b X-MS-Exchange-CrossTenant-originalarrivaltime: 15 Jun 2023 17:26:28.3524 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa X-MS-Exchange-CrossTenant-rms-persistedconsumerorg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-Transport-CrossTenantHeadersStamped: MN0PR19MB6141 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_DM4PR19MB597818A20E53C97BDB03AE8BD35BADM4PR19MB5978namp_ Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable I guess behaviour is the same in Oracle as well. Thanks, Sarwar ________________________________ From: Paul Smith* Sent: Thursday, June 15, 2023 11:48 AM To: 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 m= iddle 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 =3D '123' -- all 3 rows returned. Is it expected behavior that all 3 rows would be returned (because the spac= e isn=92t an INT?) Yes, that's totally expected behaviour. The "problem" is that it's pretty m= uch 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 lead= ing/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 *NUM= BER* 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 rel= evant 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 --_000_DM4PR19MB597818A20E53C97BDB03AE8BD35BADM4PR19MB5978namp_ Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable
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.postgresq= l.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 gro= up, 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  value= s ('123')

 

insert into matt_test  value= s (' 123')

 

insert into matt_test values ('12= 3 ')

 

select c1 from matt_test where c1= =3D '123'

-- all 3 rows returned.

 

Is it expected behavior that all = 3 rows would be returned (because the space isn=92t 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'); -- wit= h 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 sto= red

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, t= hey are space-padded or truncated as necessary to be the defined field size= .

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


Paul

--_000_DM4PR19MB597818A20E53C97BDB03AE8BD35BADM4PR19MB5978namp_--