Re: postgresql vs mysql

Tim Tassonis wrote:
> Chris wrote:
>> Erick Papadakis wrote:
>>> So how should I make a database rule in MySQL to not allow blank
>>> strings. Basically to REQUIRE a value for that column, whether it is
>>> NULL or NADA or VOID or whatever you wish to call it. I just want to
>>> make sure that something, some value, is entered for a column. Would
>>> appreciate any thoughts or pointers.
>>>
>>> Does PostgreSQL suffer from this oddity as well? This distinction
>>> between an empty string and a NULL? Could you also please give me an
>>> example of where this would be useful from a business logic
>>> standpoint? Why should a NULL be different from an empty string,
>>> what's the big mysterious difference?
>>
>> It's not an oddity.
>>
>> An empty string is a KNOWN value. You know exactly what that value is
>> - it's an empty string.
>>
>> A NULL is UNKNOWN - it doesn't have a value at all.
>
> I do still think it is a bit of an oddity, the concept of the null
> column. From my experience, it creates more problems than it actually
> solves and generally forces you to code more rather than less in order
> to achieve your goals.

Well, your mileage must vary. The absence of nulls would make my life
difficult.

Just substitute "unknown" for "null" as mentioned above and the various
operations with "null" make sense. For example, take some days and
low-temperatures:
Mon: 30
Tue: 10
Wed: 0
Thu: unknown
Fri: 0
Sat: unknown
Sun: -5

Was the low temperature the same on:
Mon/Tue: no
Wed/Fri: yes
Thu/Fri: unknown
Thu/Sat: unknown <- the always seemingly confusing null=null is null.

So what do we do without a null? Does the "helpful" app convert the
unknowns to zero? That's not right. Are we forced to specify a "special"
value like 999 for the unknown data? Then we have to add extra code to
create that value when the value is unknown and more code still to check
for that value when, say, looking for the lowest or average
temperatures. And we're set up for disaster when someone starts
measuring furnace temps instead of outdoor temps.

Look no further than Y2K to see what happened to those apps that gave
special meaning to 12/31/99.

Cheers,
Steve