From: Thorbjörn Eriksson <thorbjorn.eriksson@ec.se>
To: pgsql-sql@postgresql.org
Subject: Stripping white-space in SELECT statments
Date: Thu, 19 Sep 2002 13:20:38 +0200
Message-ID: <MFEPJLNKECPAPFDGGEIMEENJCBAA.thorbjorn.eriksson@ec.se> (raw)
Hello,
I've encountered a strange behavior in postgres 7.2.1 regarding how psql
handles strings ending with space characters.
If I want to search for records where the first column (artnrgrpmtrln_1)
begins with
'201901 ', our system that uses the database creates the following SQL
statement:
select artnrgrpmtrln_1 from sr where (artnrgrpmtrln_1>='201901 ' and
artnrgrpmtrln_1<='201901 ÿ'
The execution of this statement gives the following resultset, which I
didn't expect. What I wanted was the first 8 records only.
artnrgrpmtrln_1
--------------------------
201901 00R18000 0035C2
201901 00R18005 0035C3
201901 00R18707 007593
201901 00R28541 0030D6
201901 00R40055 0030D8
201901 00R40277T 0030D7
201901 00S00406 0030D9
201901 00SA0200 003662
201901-D00R18000 0035C2
201901-D00R18005 0035C3
201901-D00R18702 008439
201901-D00R18707 007593
201901-D00R28541 0030D6
201901-D00R40055 0030D8
201901-D00R40277T 0030D7
201901-D00S00406 0030D9
201901-D00SA0200 003662
201901JW00R18000 0035C2
201901JW00R18005 0035C3
201901JW00R18707 007593
The Table description is below.
Table "sr"
Column | Type | Modifiers
-----------------+---------------+-----------
artnrgrpmtrln_1 | character(24) |
mangd_2 | character(8) |
enhet_3 | character(1) |
text_4 | character(15) |
start_5 | character(3) |
lageruppd_6 | character(1) |
materialnr_7 | character(8) |
opfoljd_8 | character(3) |
lgst_9 | character(3) |
Indexes: sr_materialnr_7
Unique keys: sr_artnrgrpmtrln_1
This behaviour seems to have changed since postgreSQL v. 7.2, since it works
there. The reason that we don't use 'LIKE 201901 %' is that it don't use
the index sr_artnrgrpmtrln_1 when doing the lookup.
Is there anyone who can explain this behaviour? Could it be that the parser
strips of the whitespaces in '201901 '?
Best Regards,
Tobbe
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-sql@postgresql.org
Cc: thorbjorn.eriksson@ec.se
Subject: Re: Stripping white-space in SELECT statments
In-Reply-To: <MFEPJLNKECPAPFDGGEIMEENJCBAA.thorbjorn.eriksson@ec.se>
* 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