A few more comments...
> It might be useful to be able to distinguish between these two cases - not
> applicable and unknown, but there is only one option "Null" available to
> us, so we can't.
If we really need to distinguish between these two cases, I think null
shouldn't be used as a N/A value but some other like empty string or 0.
(IMHO it's preferable not to use null as N/A at all).
For example sex could be classified as
'n' - not applicable
'f' - female
'm' - male
null - yet unknown
> Example: with the customer table above you could run the following queries:
> SELECT * FROM customer WHERE sex='M';
> SELECT * FROM customer WHERE sex<>'M';
> Now you might think this returns all customers, but it will miss those
> where sex is null. You've asked for all rows where the value of sex is 'M'
> and all those with values not equal to 'M' but not rows with *no value at
> all*
these could be explained as
select all customers who surely are men
select all customers who surely aren't men
if customers sex is unknown - null, we can't decide whether they're men or
not.
> The first case can be especially confusing. Concatenating a null string to
> a string value will return null, not the original value.
Isn't it null, not null string? ;)
> Keys and nulls
> ==============
> No column that is part of a primary key can be null. When you define a
> PRIMARY KEY, none of the columns mentioned can take a null value.
> Postgresql makes sure of this by defining the columns as NOT NULL for you.
... because primary keys are to uniquelly identify rows in a table, and
how's an unknown values going to do that :)
--
Antti Haapala
+358 50 369 3535
ICQ: #177673735
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
