pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mark <mark@zserve.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Case Insensitive Queries
Date: Wed, 30 May 2001 12:53:22 -0400
Message-ID: <24650.991241602@sss.pgh.pa.us> (raw)
In-Reply-To: <200105301549.f4UFnfW16237@mail.ldssingles.com>
References: <200105301549.f4UFnfW16237@mail.ldssingles.com>

Mark <mark@zserve.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



view thread (19+ messages)  latest in thread

Message-ID: <24650.991241602@sss.pgh.pa.us>
Permalink:  ../24650.991241602@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/24650.991241602@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, mark@zserve.com
  Subject: Re: Case Insensitive Queries
  In-Reply-To: <24650.991241602@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox