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
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 |
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 |