From: | Paul Smith* <paul(at)pscs(dot)co(dot)uk> |
---|---|
To: | pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | Re: The same result for with SPACE and without SPACE |
Date: | 2023-06-15 15:48:18 |
Message-ID: | fbae23da-7761-161d-b484-6c38e2522127@pscs.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-advocacy |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2023-06-15 15:53:20 | Re: The same result for with SPACE and without SPACE |
Previous Message | Wetmore, Matthew (CTR) | 2023-06-15 15:21:56 | The same result for with SPACE and without SPACE |
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2023-06-15 15:53:20 | Re: The same result for with SPACE and without SPACE |
Previous Message | Wetmore, Matthew (CTR) | 2023-06-15 15:21:56 | The same result for with SPACE and without SPACE |