Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4UGwrE69583 for ; Wed, 30 May 2001 12:58:53 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.3/8.11.3) with ESMTP id f4UGrMR24653; Wed, 30 May 2001 12:53:22 -0400 (EDT) To: Mark cc: pgsql-sql@postgresql.org Subject: Re: Case Insensitive Queries In-reply-to: <200105301549.f4UFnfW16237@mail.ldssingles.com> References: <200105301549.f4UFnfW16237@mail.ldssingles.com> Comments: In-reply-to Mark message dated "30 May 2001 08:57:51 -0600" Date: Wed, 30 May 2001 12:53:22 -0400 Message-ID: <24650.991241602@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200105/337 X-Sequence-Number: 3178 Mark writes: > 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? regression=# create table foo (f1 char(20)); CREATE regression=# insert into foo values ('zz'); INSERT 800569 1 regression=# select * from foo; f1 ---------------------- zz (1 row) regression=# select * from foo where f1 = 'zz'; f1 ---------------------- zz (1 row) regression=# You'll need to be more specific about what you're unhappy about. > Varchars would incur performance penalties I want to try to avoid if at > all possible. You are operating under misinformation about what's efficient or not. There are no performance penalties that I know of for varchar ... if anything, bpchar is the less efficient choice, at least in Postgres. The extra I/O costs for those padding blanks add up, and there's no compensatory savings anywhere. In any case, if your data is really variable-length strings, forcing it into a datatype that doesn't match its semantics because of dubious micro-efficiency considerations is just plain bad database design. Rather than having blanks that you want to pretend aren't there, you should not have the blanks in the first place. IMHO anyway. regards, tom lane