Обсуждение: Re: entering empty value

> I have a table with records where sometimes a value should be kept empty
> e.g. faxnumber is sometime empty because not everybody owns a fax. What
> is the syntax for entering the data for such a record with  an empty
> value?
>

create table t (a int, b text);

insert into t values(1, NULL)

> > I have a table with records where sometimes a value should be kept empty
> > e.g. faxnumber is sometime empty because not everybody owns a fax. What
> > is the syntax for entering the data for such a record with  an empty
> > value?
>
> create table t (a int, b text);
>
> insert into t values(1, NULL)

Actually, to be anal, a NULL value is used to denote an undefined or unknown
value. If the value is known to be empty (and assuming this is stored in a
string field such as TEXT or VARCHAR), it might be more appropriate to say:

insert into t values (1,'');

This is especially true as empty strings can be used in an index, while NULL
values, to the best of my knowledge, do not appear in indexes.

Greg

"Gregory Wood" <gregw@com-stock.com> writes:
> Actually, to be anal, a NULL value is used to denote an undefined or unknown
> value. If the value is known to be empty (and assuming this is stored in a
> string field such as TEXT or VARCHAR), it might be more appropriate to say:

> insert into t values (1,'');

Indeed, it seems that a frequent novice error is to confuse NULL with a
zero-length string.  They are completely different concepts; the sooner
you grasp that, the happier you'll be in working with SQL.  As Greg
says, NULL is best considered a placeholder indicating "value unknown"
or "value not present".  It is *never* equivalent to *any* legitimate
value of a datatype domain.

> This is especially true as empty strings can be used in an index, while NULL
> values, to the best of my knowledge, do not appear in indexes.

Postgres' btree indexes do index nulls.  However, at present this is at
best an academic curiosity, because our index access method API doesn't
provide any way to use an index to search for nulls.  (Technically,
"foo IS NULL" isn't an indexable operator.)  Fixing that is on the
might-happen-someday list.  In the meantime, it's best not to design
your data conventions in a way that requires you to search for nulls
often...

            regards, tom lane