Re: Efficiency question: VARCHAR with empty string vs NULL

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Doug Fields <dfields-pg-general(at)pexicom(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Efficiency question: VARCHAR with empty string vs NULL
Date: 2002-05-24 18:54:14
Message-ID: 13510.1022266454@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-sql

Doug Fields <dfields-pg-general(at)pexicom(dot)com> 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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Neil Conway 2002-05-24 19:00:22 Re: Case Insensitive Data Type
Previous Message Neil Conway 2002-05-24 18:52:08 Re: Altering existing table to be WITHOUT OIDs

Browse pgsql-sql by date

  From Date Subject
Next Message Doug Fields 2002-05-24 19:10:45 Re: Efficiency question: VARCHAR with empty string
Previous Message Douglas Trainor 2002-05-24 18:41:42 Re: Trees in SQL