Re: RFC: A brief guide to nulls - Mailing list pgsql-sql

I think that having this topic defined and available
will be of great benefit... !!! Thanks Richard.

Some additional thoughts based upon what other people
have explicitly or implicitly implied.  Peter quoted the sql
definition as:   >Every data type includes a special value, called the null value,
and my earlier post implied that, but...

I believe that one needs to be VERY explicit in the
distinction between sementic meaning of some field
and the values accessable.  Using the example proposed
in earlier posts, the storing of sex field for a customer.

The example query was:
>  SELECT * FROM customer WHERE sex='M';
>  SELECT * FROM customer WHERE sex<>'M';

One person suggested:
>For example sex could be classified as
> 'n'  - not applicable
> 'f'  - female
> 'm'  - male
> null - yet unknown
>...
>if customers sex is unknown - null, we can't decide
>whether they're men or not.

Which is straying way into the semantic information of
the field. In reality we have:

+Fieldname(sex)------------------+
|..............|value|{somevalue}|
|..............|nullP|{1or0}     |
+--------------------------------+

Every field has a value portion and a nullP portion.
Here I use nullP, which is either 1 or 0, indicating
that the field is NULL or NOTNULL.

NOTNULL means that the value field has a value.
NULL means that the value field does not have a value.

Period. Anything else begins to stray into the semantic
range. Using the above...

> 'n'  - not applicable
> 'f'  - female
> 'm'  - male
> null - yet unknown

which we could code as..
.....................................meaning
+Fieldname(sex)------------------+
|..............|value|{n}        |   not applicable
|..............|nullP|{0}        |
+--------------------------------+
+Fieldname(sex)------------------+
|..............|value|{f}        |   female
|..............|nullP|{0}        |
+--------------------------------+
+Fieldname(sex)------------------+
|..............|value|{m}        |   male
|..............|nullP|{0}        |
+--------------------------------+
+Fieldname(sex)------------------+
|..............|value|{}         |   
|..............|nullP|{1}        |   <no value>
+--------------------------------+

If I was a clerk, looking at a person and could not
tell their sex due to appearance, etc, that should
probably be coded: 'u'-unknown

The "concept" of null meaning unknown is SEMANTIC...
in the case of sex.

The nullP, ie testing via NULL/NOTNULL, means that
one can either test or (should/can) access the value field.
No other meaning should be implied. The application
on up the ladder implies the meaning behind not having
a value.

The point I'm trying to make here is that one should not
intermix the application meaning of having/not-having
a value with the value itself.

Especially since you can have a NOTNULL field that
has a text value having no bytes in it, i.e. a string
field that has no characters...

Humm... don't know if I made my point clear as mud...


Best regards,

.. Otto