Received: from foghorn.airs.com (foghorn.airs.com [63.201.54.26]) by postgresql.org (8.11.3/8.11.1) with SMTP id f4UJpiE47441 for ; Wed, 30 May 2001 15:51:45 -0400 (EDT) (envelope-from ian@airs.com) Received: (qmail 20577 invoked by uid 10); 30 May 2001 19:51:42 -0000 Received: (qmail 15224 invoked by uid 269); 30 May 2001 19:51:03 -0000 To: Mark Cc: Tom Lane , pgsql-sql@postgresql.org Subject: Re: Case Insensitive Queries References: <200105301549.f4UFnfW16237@mail.ldssingles.com> <24650.991241602@sss.pgh.pa.us> <200105301857.f4UIvoW17610@mail.ldssingles.com> From: Ian Lance Taylor Date: 30 May 2001 12:51:03 -0700 In-Reply-To: <200105301857.f4UIvoW17610@mail.ldssingles.com> Message-ID: Lines: 57 User-Agent: Gnus/5.0808 (Gnus v5.8.8) Emacs/20.7 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Archive-Number: 200105/344 X-Sequence-Number: 3185 Mark 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