Re: Case Insensitive Queries

Mark <mark(at)zserve(dot)com> 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