Re: null vs empty string

On 2 July 2010 00:58, Bastiaan Olij <lists(at)basenlily(dot)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