Re: null vs empty string
От | Thom Brown |
---|---|
Тема | Re: null vs empty string |
Дата | |
Msg-id | AANLkTilEsUTIeUkZCX9Vc14kciFiuvFBRRE-yen4K_Zi@mail.gmail.com обсуждение исходный текст |
Ответ на | null vs empty string (Kent Thomas <kent@solarbee.com>) |
Ответы |
Re: null vs empty string
(Bastiaan Olij <lists@basenlily.nl>)
|
Список | pgsql-novice |
On 2 July 2010 00:58, Bastiaan Olij <lists@basenlily.nl> wrote: > Hi all, > > Just to confuse matters more, this is how I try to think of NULL. > > I have a field called ANSWER_TO_QUESTION. > > What is the difference between this field being NULL, and this field > being empty? > > NULL means the question hasn't been answered yet, thus there is no > answer, there may be an answer once it is provided so you can't really > say the answer is empty. Empty would mean the answer has been given and > the answer is empty. > > Comparing something that doesn't exist is there for an impossibility. > > The debate becomes interesting when you look at for instance a table > with info about persons. You would store a first name, a middle name, > and a last name. Many people do not have a middle name. Should this > field then be NULL or should it be empty? > > Following the statement above this value should be: > NULL if you do not know the middle name of the person (the question has > not been answered yet, the person may actually have a middle name) > empty if that person doesn't have a middle name, the question has been > answered and the answer is an empty string > > When concatenating the string to get the full name of the person, > because it is NULL, you can't do a concatenation with it. Since you do > not know the middle name, you can't know the full name. Equally so, if > you want to find all people who do not have a middle name, you don't > want this person to come up, yes the middle name isn't given, but it is > also not given as empty. That person may indeed have a middle name. > > Whether making your middle name column nullable makes sense is a totally > different discussion. > For this particular field I would say a nullable middle name is > completely impractical. Yes you may not know the middle name of a person > so in theory you should save the middle name as NULL, but it really only > gets in your way. I would just simply say that if you don't know the > middle name, you don't care, and an empty string makes your life so much > easier. If you somehow want to flag that the middle name is unknown > because you want someone to come in after you and complete the data, > flag it in some other way that doesn't mean you have to make exceptions > everywhere in your queries to check for NULL. > > But say the field was in a table containing answers to a question sheet, > I definitely want to make a difference between questions that haven't > been answered, and questions to which the answer was an empty string (or > for a numeric, a difference between not having answered and the answer > being 0). > > One last one to trick the minds on the person example, birthday:) > NULL means: I don't know the persons birthday, so I don't know the > persons age, I can't calculate the age, I can't select the person based > on his/her date of birth > empty (does postgres even support this?) means: the person hasn't been > born yet, their age is 0, they definately stand appart from people who > have been born but who's date of birth is unknown. > Not something many would run into, but if you write software for a > maternity ward it could come in very handy to see the difference in these... > > Cheers, > > Bas > > Your message only went to me, so thought I'd share it with the list. (see above) Thom
В списке pgsql-novice по дате отправления: