Re: [GENERAL] why the need for is null?
Martijn van Oosterhout wrote: On Thu, Jan 01, 2004 at 11:53:29PM +0100, Baldur Norddahl wrote: Ok, but since this can be quite annoying and unexpected, could we get an operator that does not use tristate logic but simply compares? Maybe == which seems to be free :-) So X==Y is true if X and Y are equal or both are null, false othervise. Annoying, not really. It's actually extremely useful. It's useful having a value which is never equal to anything else, not even itself. If you use it to represent unknown it will work for you. If you try to use it for anything else, it will bite you. You could create a new operator, but that means you'll have difficulty moving it to any database that doesn't have that operator (which is most of them). If you want it to match perhaps you should forget NULL and use '' (zero length string) instead. Don't mentioning the fact that for Oracle a zero length string is NULL! Isn't that funny ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] why the need for is null?
Quoting Guy Fraser [EMAIL PROTECTED]: There already is an operator, and it is the ANSI SQL operator IS. Just because IS does not use puctuation characters does not mean it is not an operator. IS is not an operator in postgresql, at least not in the same sense that = is an operator. You can not do \do is while \do = works fine. select coalesce(string_column,'') from some_table ; Will postgresql still make effective use of the indexes if I use a function like coalesce on the column before comparing it? Even if it does, the method I already use is more effective. If you still don't understand, then use MySQL it is messed up and allows weird things like most of what you want to do. 1) I understand the issues involved perfectly. I just happens to have a table where it would be usefull that NULL=NULL is true. It is not so, and therefore I have to use a syntax that is hard to read and I have been made to understand that I will have to accept that. Fine. 2) What kind of crap is that flaming me like this? Do all users that ask a question about why postgresql or the sql standard implements a feature in a specific way, end up being told to switch to mysql? 3) Mysql knows how to compare nulls: mysql select null=null; +---+ | null=null | +---+ | NULL | +---+ 1 row in set (0.01 sec) mysql select null is null; +--+ | null is null | +--+ |1 | +--+ 1 row in set (0.00 sec) Baldur This message was sent using IMP, the Internet Messaging Program. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] why the need for is null?
Hi all; Here is a brief guide to NULL's and Referential Integrity: NULL is a special SQL value meaning 'unknown.' Well, it is a little more complicated and NULL can mean value does not exist. Therefore X = NULL is NULL becuase we don't know if the NULL is equal to X. So: NULL does not equal NULL NULL does not equal anything else. NULL does not equal an empty string You get the picture. Think of it this way: 2 non-existant or unknown values don't equal any other existant or non-existant value, known or unknown. Now, referential integrity is defined as follows: For every non-NULL foreign key, there is a corresponding primary key in the referenced table. Note that NULL's are specifically handled in the RI definition. When do I use NULL's? NULL's have a few uses: 1: To indicate that the foreign key does NOT correspond with a primary key in the referencing table. In this case, NULL means something like value does not exist (this is not the same as an empty value). In an employee's table, a NULL in the manager's field would mean Employee does not report to any other employee as a manager. 2: To indicate that the value probably exists, but is unknown at present (we don't know this customer's address, so we set it to NULL). Best Wishes, Chris Travers ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] why the need for is null?
Baldur Norddahl wrote: Quoting Martijn van Oosterhout [EMAIL PROTECTED]: ... You could create a new operator, but that means you'll have difficulty moving it to any database that doesn't have that operator (which is most of them). Any commercial database vendor would be happy to make such a feature just for that reason: to lock me in to their database :-). I do not try to stay database neutral, and use lots of other features that will only work in postgresql. There already is an operator, and it is the ANSI SQL operator IS. Just because IS does not use puctuation characters does not mean it is not an operator. If you want it to match perhaps you should forget NULL and use '' (zero length string) instead. An empty string is an empty string, and a NULL is the lack of there being a string, they are not the same. If you want to emulate what you have proposed then use the function coalesce. Example: select coalesce(string_column,'') from some_table ; This will return an empty string for all records that have no data in string_column. I have designed a number of realtime data collection programs, and when inserting only the available data into the proper columns of the table, I often end up will NULL columns because there was no data for that column. It is very usefull to know if you had data available or if the data was 0 or an empty string. If you still don't understand, then use MySQL it is messed up and allows weird things like most of what you want to do. Happy New Year ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] why the need for is null?
Tom Lane wrote: Not sure that it's fair to characterize this as a property of the relational model. It is a property of the SQL standard. Yes indeed - I fell into the classic Relational model and SQL are not the same thing trap ! Mark ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] why the need for is null?
Quoting Martijn van Oosterhout [EMAIL PROTECTED]: Annoying, not really. It's actually extremely useful. It's useful having a value which is never equal to anything else, not even itself. If you use it to represent unknown it will work for you. If you try to use it for anything else, it will bite you. I need it to represent empty because the field in question is a foreign key to another table. If it represented unknown the foreign key should block it as it could not possible know if that unknown value was valid. But I can't argue against the SQL standard of course. You could create a new operator, but that means you'll have difficulty moving it to any database that doesn't have that operator (which is most of them). Any commercial database vendor would be happy to make such a feature just for that reason: to lock me in to their database :-). I do not try to stay database neutral, and use lots of other features that will only work in postgresql. If you want it to match perhaps you should forget NULL and use '' (zero length string) instead. Then I need to have a meaningless entry in the foreign table, and fill my code with special cases that filter out that fake entry before showing the data to the user. Besides who said I didn't want to allow the empty string as valid data? This would be even more an issue if the field was a nummeric, where any nummeric value is ok. If I can not use NULL to represent empty or not applicateable I would have to make a special field that tells me if I should ignore the previous field or not. Does not sound reasonable when NULL works fine for just that. The best compromise I found so far is this X=Y or X is null and Y is null construct. Just looks hard to understand and cumpersome for someone which is not expert on this issue. Baldur This message was sent using IMP, the Internet Messaging Program. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
