Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id DA4859FBE44 for ; Thu, 26 Apr 2007 05:35:32 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 81902-02 for ; Thu, 26 Apr 2007 05:35:25 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from oxford.xeocode.com (unknown [87.127.95.194]) by postgresql.org (Postfix) with ESMTP id 814A59FBB52 for ; Thu, 26 Apr 2007 05:35:28 -0300 (ADT) Received: from localhost ([127.0.0.1] helo=oxford.xeocode.com) by oxford.xeocode.com with esmtp (Exim 4.63) (envelope-from ) id 1HgzRl-0001Id-1S; Thu, 26 Apr 2007 09:35:25 +0100 From: Gregory Stark To: "Jim Nasby" Cc: "Merlin Moncure" , "chrisj" , Subject: Re: seeking advise on char vs text or varchar in search table In-Reply-To: (Jim Nasby's message of "Wed, 25 Apr 2007 19:30:41 +0200") Organization: EnterpriseDB References: <10103002.post@talk.nabble.com> X-Draft-From: ("nnimap+mail01.enterprisedb.com:INBOX.performance" 30) Date: Thu, 26 Apr 2007 09:35:24 +0100 Message-ID: <87abwva5yr.fsf@oxford.xeocode.com> User-Agent: Gnus/5.110006 (No Gnus v0.6) Emacs/21.4 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/421 X-Sequence-Number: 24298 "Jim Nasby" writes: > AIUI, char, varchar and text all store their data in *exactly* the same way in > the database; char only pads data on output, and in the actual tables it still > contains the regular varlena header. The only reason I've ever used char in > other databases is to save the overhead of the variable-length information, so > I recommend to people to just steer clear of char in PostgreSQL. Everything you said is correct except that char actually pads its data on input, not output. This doesn't actually make a lot of sense since we're storing it as a varlena so we could pad it on output and modify the data type functions to pretend the spaces are there without storing them. However it would only make a difference if you're storing variable length data in a char field in which case I would 100% agree with your conclusion and strongly recommend using varchar. The only reason I would think of using char is when the data should always be the same length, like a SSN or md5hash or something like that. In which case it's purely for the self-documenting notational convenience, not any performance reason. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com