Null values are a big surprise to almost every end-user (though the
programmers are OK with them).
Look at the astonishment on the face of your end user when you tell them
that:
SELECT COUNT(*) FROM clothing WHERE clothing_color = 'green'
+
SELECT COUNT(*) FROM clothing WHERE NOT clothing_color = 'green'
Is not the count of all clothing because clothing without a color
recorded will not be counted.
Or (perhaps better yet, violating trichotomy) ...
If <Some_column> has a null numeric value, then ALL of the following are
FALSE for that case:
Some_column < 0
Some_column > 0
Some_column = 0
Some_column <> 0 // This is the one that many find surprising
Some_column <= 0
Some_column >= 0
You can probably see why Null values can do strange things in (for
instance) an index.
Even at that, I think that being able to insert more than one null value
into a unique index should be considered as a bug (or diagnosed as an
error).
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of
vhikida@inreach.com
Sent: Wednesday, January 19, 2005 3:30 PM
To: J. Greenlees
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Unique Index
According to Date you should never use NULLs. This is because a NULL can
mean many different things. It can mean not known (e.g. I know he has an
age but I don't know what it is), It can be not applicable (e.g. in a
Party table of organizations and people, people would be of a certain
sex
but an organization would not), It can mean a number divided by zero, It
can also mean I don't know if the value is unknown or if the value is
not
applicable etc. etc. In Date's view there is an infinite number of
meanings for null and for a database to handle it correctly would
require
an equivalent set of operators for each type of null.
I think though that Nulls are a very useful feature of SQL databases.
It's
just that when I write a program, I have to know in a particular context
what a null means and handle it appropriately (of course controlling
nulls
in an index is a different matter). I don't think there is only one
accepted way to use nulls.
