pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Paul Smith* <paul@pscs.co.uk>
To: pgsql-admin@lists.postgresql.org
Subject: Re: The same result for with SPACE and without SPACE
Date: Thu, 15 Jun 2023 16:48:18 +0100
Message-ID: <fbae23da-7761-161d-b484-6c38e2522127@pscs.co.uk> (raw)
In-Reply-To: <3f21240f59a54f96898886f2c9189cf3@express-scripts.com>
References: <DM4PR19MB5978EB964FA1C7B55FFCB02DD35AA@DM4PR19MB5978.namprd19.prod.outlook.com>
	<DM4PR19MB59782360E0C452810D70CBE8D35AA@DM4PR19MB5978.namprd19.prod.outlook.com>
	<B600CCAA-59E5-4907-8E6D-C645CA24AB1D@elevated-dev.com>
	<DM4PR19MB59784B518F25D2C2292CA3FBD35AA@DM4PR19MB5978.namprd19.prod.outlook.com>
	<2018059.1686782739@sss.pgh.pa.us>
	<0055148e2cc742cbbc23485ae0c6c04e@express-scripts.com>
	<2CAFD37D-68FC-4A01-BE65-FAF1ADF7A60A@elevated-dev.com>
	<CAJXhg9ByRV9QPVcW5MPi2c20g948Oxt8qU1DrcVb51fFgTEj2A@mail.gmail.com>
	<1726236757.407336.1686838293703@mail.yahoo.com>
	<3f21240f59a54f96898886f2c9189cf3@express-scripts.com>

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

view thread (22+ messages)  latest in thread

Message-ID: <fbae23da-7761-161d-b484-6c38e2522127@pscs.co.uk>
Permalink:  ../fbae23da-7761-161d-b484-6c38e2522127@pscs.co.uk/
Also on:    postgresql.org/message-id/fbae23da-7761-161d-b484-6c38e2522127@pscs.co.uk

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: paul@pscs.co.uk, pgsql-admin@lists.postgresql.org
  Subject: Re: The same result for with SPACE and without SPACE
  In-Reply-To: <fbae23da-7761-161d-b484-6c38e2522127@pscs.co.uk>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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