Re: Null comparisons (was Re: checksum)

Marco Colombo <pgsql(at)esiway(dot)net> writes:

> I'm not against abusing of the db, nor playing dirty tricks, if that fits
> your needs. You're free to design your db the way you like and face
> the cost of a careful design or of later SQL gymnastics. I'm fine,
> as long as you don't ask for syntactic sugar to support those "features".

My point is that you're making judgements about his schema without actually
knowing what you're talking about. For all we know his schema is entirely
reasonable and it's the query that has unusual requirements. Spouting general
design principles that may or may not apply as being iron-clad rules and
saying it's just wrong to break them is wilful blindness.

Database modelling is not something you can do by holding up some textbook and
screaming "third normal form" until the developers trying to get work done
cower in submission. There are lots of times when breaking or bending the
rules is entirely reasonable and blindly following them is simply a waste of
time.

For example, I have a table that uses NULLs to represent absent data. In 90%
of the queries three value logic is just exactly what's needed. In any case
they each have foreign key dependencies and having special values to represent
the absent values would be a major pain. It would require satisfying the
foreign keys with bogus records.

However I have queries that have to match provided data with other records,
including having missing data in the same position. For such a query I need to
break the usual model of three value logic and write something similar to what
this user needs. In my case no index would really be reasonable since there
are half a dozen such fields, but in general there's no reason an index
shouldn't be available for such cases.

--
greg