From: Ian Lance Taylor <ian@airs.com>
To: Mark <mark@zserve.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: Case Insensitive Queries
Date: 30 May 2001 12:51:03 -0700
Message-ID: <si8zjey5so.fsf@daffy.airs.com> (raw)
In-Reply-To: <200105301857.f4UIvoW17610@mail.ldssingles.com>
References: <200105301549.f4UFnfW16237@mail.ldssingles.com>
<24650.991241602@sss.pgh.pa.us>
<200105301857.f4UIvoW17610@mail.ldssingles.com>
Mark <mark@zserve.com> writes:
> with varchars, as I understand it (and postgresql may be different),
> each varchar field has a header that stores the length of the particular
> entry's length. Further, if the varchar field precedes another field,
> the system loses the ability to use fixed-length addressing to access
> the field after the varchar, since the system must determine on a
> case-by-case basis how to access the field after the varchar. It has to
> calculate the size of the varchar, add that to the start of the varchar
> (plus header length), and then it has the address of the next field.
> With non-variant char it is fixed length, so selects and updates operate
> much more quickly. Even the postgresql documentation asserts something
> similar to this:
>
> 'Both TEXT and VARCHAR() store only the number of characters in the
> string. CHAR(length) is similar to VARCHAR(), except it always stores
> exactly length characters. This type pads the value with trailing spaces
> to achieve the specified length, and provides slightly faster access
> than TEXT or VARCHAR().'
>
> Perhaps I am misinformed.
You are misinformed with respect to Postgres. The implementation of
CHAR(length) is just like the implementation of VARCHAR(). It stores
a header with the entry length. That header is considered in all
functions in the same way that the VARCHAR() header is.
I don't know what documentation you are quoting, but it seems somewhat
misleading to me.
Here is the comment from the implementation
(src/backend/utils/adt/varchar.c):
/*
* CHAR() and VARCHAR() types are part of the ANSI SQL standard. CHAR()
* is for blank-padded string whose length is specified in CREATE TABLE.
* VARCHAR is for storing string whose length is at most the length specified
* at CREATE TABLE time.
*
* It's hard to implement these types because we cannot figure out
* the length of the type from the type itself. I change (hopefully all) the
* fmgr calls that invoke input functions of a data type to supply the
* length also. (eg. in INSERTs, we have the tupleDescriptor which contains
* the length of the attributes and hence the exact length of the char() or
* varchar(). We pass this to bpcharin() or varcharin().) In the case where
* we cannot determine the length, we pass in -1 instead and the input string
* must be null-terminated.
*
* We actually implement this as a varlena so that we don't have to pass in
* the length for the comparison functions. (The difference between these
* types and "text" is that we truncate and possibly blank-pad the string
* at insertion time.)
*
* - ay 6/95
*/
Ian
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: ian@airs.com, mark@zserve.com, tgl@sss.pgh.pa.us
Subject: Re: Case Insensitive Queries
In-Reply-To: <si8zjey5so.fsf@daffy.airs.com>
* 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