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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of vhikida@xxxxxxxxxxx Sent: Wednesday, January 19, 2005 3:30 PM To: J. Greenlees Cc: pgsql-general@xxxxxxxxxxxxxx Subject: Re: 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. ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx
