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 0035C2201901 00R18005 0035C3201901 00R18707 007593201901 00R28541
0030D6201901 00R40055 0030D8201901 00R40277T 0030D7201901 00S00406 0030D9201901 00SA0200 003662201901-D00R18000
0035C2201901-D00R18005 0035C3201901-D00R18702 008439201901-D00R18707 007593201901-D00R28541 0030D6201901-D00R40055
0030D8201901-D00R40277T0030D7201901-D00S00406 0030D9201901-D00SA0200 003662201901JW00R18000 0035C2201901JW00R18005
0035C3201901JW00R18707 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