Received: from anchor-post-32.mail.demon.net (anchor-post-32.mail.demon.net [194.217.242.90]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4UI4ME00590 for ; Wed, 30 May 2001 14:04:22 -0400 (EDT) (envelope-from dev@archonet.com) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-32.mail.demon.net with esmtp (Exim 2.12 #1) id 155ALW-00093k-0W; Wed, 30 May 2001 19:05:26 +0100 Received: from client (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with SMTP id A2BFF12A37; Wed, 30 May 2001 18:34:48 +0100 (BST) Message-ID: <005701c0e92e$d2f1c000$1001a8c0@archonet.com> From: "Richard Huxton" To: "Mark" , References: <200105301551.f4UFpGW16248@mail.ldssingles.com> Subject: Re: bpchar compares (was Re: Case Insensitive Queries) Date: Wed, 30 May 2001 18:34:38 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200105/339 X-Sequence-Number: 3180 From: "Mark" > It appears that the behavior of a bpchar compare with a string literal > is not implicitly trimming the bpchar before the compare, which IMHO is > incorrect behavior. Is my opinion valid? If so, how difficult of a fix > would this be in terms of time and effort? Should I submit a bug report > to another list, or is a developer receiving this? Is this a feature? bpchar==char==fixed, space padded AFAIK which means correct behaviour > This is an important issue for me, because I am converting a db from MS > SQL to postgresql. The MS SQL database uses bpchar (or just char in MS > SQL terms) because performance is slightly better; the compares > automatically trim the blanks off of the char at compare time. I have > over 150 tables to work with, and I would rather not have to change them > from bpchar to varchar, not to mention the performance decrease this > might incur. Surely it's just a search & replace. No noticable performance issues with char vs varchar that I've found. > You might be thinking, 'just use trim(username) everywhere you compare'. > Yes, that is a solution, but not a practical one in my case. If this is > a bug, I don't want to hack around it: I'd rather wait for the fix. > Varchars would incur performance penalties I want to try to avoid if at > all possible. Try some testing with varchar - I'm not sure you are going to suffer much. - Richard Huxton