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...
Your message only went to me, so thought I'd share it with the list. (see above)
In response to
pgsql-novice by date
|Next:||From: Bastiaan Olij||Date: 2010-07-02 09:50:01|
|Subject: Re: null vs empty string|
|Previous:||From: A. Kretschmer||Date: 2010-07-02 07:17:06|
|Subject: Re: How to use search_path in CASE|