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 1q9pHw-0003EL-RH for pgsql-admin@arkaria.postgresql.org; Thu, 15 Jun 2023 15:53:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q9pHv-0007Wl-MX for pgsql-admin@arkaria.postgresql.org; Thu, 15 Jun 2023 15:53:31 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q9pHv-0007V9-7j for pgsql-admin@lists.postgresql.org; Thu, 15 Jun 2023 15:53:31 +0000 Received: from mail.pscs.co.uk ([178.159.9.185]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1q9pHs-002YiH-BE for pgsql-admin@lists.postgresql.org; Thu, 15 Jun 2023 15:53:30 +0000 Authentication-Results: mail.pscs.co.uk; spf=none; auth=pass (cram-md5) smtp.auth=pscs Received: from lmail.pscs.co.uk ([192.168.150.1]) by mail.pscs.co.uk ([192.168.150.185] running VPOP3) with ESMTPSA (TLSv1.3 TLS_AES_256_GCM_SHA384) for ; Thu, 15 Jun 2023 16:53:25 +0100 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pscs.co.uk; q=dns/txt; s=lmail; h=Content-Type:Message-ID:Date:MIME-Version:Subject:To:References:From :In-Reply-To:Cc:Content-Transfer-Encoding:Reply-to:Sender; t=1686844101; x=1687448901; bh=xlhbSUheExult2KVpsGlR8fkuexnaarxi3lY+xpmdXo=; b=LA0dEH5WvmZrywVZuZKS3v0Miiw+Sp3t+juGejPZKri9JlSWO8t3k6/NahoGcZTX5ln8P0BN 4RUoErF6+hXvr04qZ8r0V+uItrDHj8THnN8IbvecJgktqEH2SMwNKMz74vpNBGqjYnIm0jw1X+ E6ovGQNug9a1CPvWI2K0pxGtM= Authentication-Results: lmail.pscs.co.uk; spf=none; auth=pass (cram-md5) smtp.auth=paul Received: from [192.168.6.10] ([192.168.150.64]) by lmail.pscs.co.uk ([192.168.150.70] running VPOP3) with ESMTPSA (TLSv1.3 TLS_AES_256_GCM_SHA384) for ; Thu, 15 Jun 2023 16:48:21 +0100 Content-Type: multipart/alternative; boundary="------------0xpKjnewZkLKVkcpcAQol0SU" Message-ID: Date: Thu, 15 Jun 2023 16:48:18 +0100 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:102.0) Gecko/20100101 Thunderbird/102.11.2 Subject: Re: The same result for with SPACE and without SPACE To: pgsql-admin@lists.postgresql.org 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> From: Paul Smith* In-Reply-To: <3f21240f59a54f96898886f2c9189cf3@express-scripts.com> X-Authenticated-Sender: paul X-Server: VPOP3 Enterprise V8.5 - Registered X-Organisation: Paul Smith Computer Services X-VPOP3Tester: 12 345 X-Authenticated-Sender: pscs List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------0xpKjnewZkLKVkcpcAQol0SU Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 --------------0xpKjnewZkLKVkcpcAQol0SU Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
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

--------------0xpKjnewZkLKVkcpcAQol0SU--