Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 675DC476876 for ; Thu, 19 Sep 2002 07:03:48 -0400 (EDT) Received: from ec.se (bowser.ec.se [213.88.243.12]) by postgresql.org (Postfix) with ESMTP id 3192C476453 for ; Thu, 19 Sep 2002 07:03:47 -0400 (EDT) Received: from tobbes [62.119.73.5] by ec.se [213.88.243.12] with SMTP (MDaemon.v3.5.6.R) for ; Thu, 19 Sep 2002 13:03:00 +0200 From: =?iso-8859-1?Q?Thorbj=F6rn_Eriksson?= To: Subject: Stripping white-space in SELECT statments Date: Thu, 19 Sep 2002 13:20:38 +0200 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 Importance: Normal X-MDRemoteIP: 62.119.73.5 X-Return-Path: thorbjorn.eriksson@ec.se X-MDaemon-Deliver-To: pgsql-sql@postgresql.org Reply-To: thorbjorn.eriksson@ec.se X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200209/212 X-Sequence-Number: 9489 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