Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 89ADD47623D for ; Fri, 24 May 2002 15:01:36 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 8FD80476272 for ; Fri, 24 May 2002 14:54:14 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g4OIsEW13513; Fri, 24 May 2002 14:54:14 -0400 (EDT) To: Doug Fields Cc: pgsql-general@postgresql.org Subject: Re: Efficiency question: VARCHAR with empty string vs NULL In-reply-to: <5.1.0.14.2.20020524131720.02c09fa0@mail.pexicom.com> References: <5.1.0.14.2.20020524131720.02c09fa0@mail.pexicom.com> Comments: In-reply-to Doug Fields message dated "Fri, 24 May 2002 13:20:43 -0400" Date: Fri, 24 May 2002 14:54:14 -0400 Message-ID: <13510.1022266454@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200205/899 X-Sequence-Number: 24879 Doug Fields writes: > Is there a performance difference with VARCHAR elements of value NULL and > zero-length string? These are not semantically equivalent (if you think they are, you've been using Oracle too long). You will almost certainly regret it if you try recoding your app to make them equivalent. An example: regression=# select 'foo'::varchar || ''::varchar; ?column? ---------- foo (1 row) regression=# select 'foo'::varchar || null::varchar; ?column? ---------- (1 row) But to answer your question, NULLs might save a couple bytes if there are multiple NULLs per row. I think that replacing a single empty varchar with a NULL would net out to no change (you lose the 4-byte varchar length word, but have to store a null-values bitmap instead), depending on alignment issues and how many columns there are in the table. regards, tom lane