In response to "James B. Byrne" <byrnejb@harte-lyne.ca>:
>
> The basic issue is episodic duration, expressed as columns named
> dt_effective_from and dt_superseded_after. Both are datetime types
> containing values normalized to utc. You see where this is going.
>
> The issue is what to enter when the value is known to be unknown, as
> in some indeterminate future date, which may be never. I read that
> relational set values should never be null, as null is indeterminate
> for WHERE clauses and may result in unexpected results.
Where'd you read that? There are very specific rules for how NULL
behaves within WHERE clauses. I expect that whoever wrote what you
read didn't know those rules, or wrote them based on a DB system that's
less well documented than PostgreSQL.
Of course, if you don't know the rules, you don't know what to expect,
thus the results can be unexpected.
> On the
> other hand, setting some artificially excessive future date seems in
> its place seems, to me, to have its own problems.
IMHO, the use of magic values should have been deprecated in the 70s.
NULL means NULL, infinity means infinity. Trying to use NULL to mean
anything other than "no value provided" is begging for trouble. Putting
magic values in like "infinity in this field means that we have not
yet calculated the value" or something similar is equally problematic.
> Since this issue must have been dealt with time and time again in
> the past I would like to know what, if any, consensus has been
> reached on the matter. What is the best way to proceed?
Depends on what you're trying to accomplish. However, the most
unambiguous way to solve the problem is to solve it unambiguously:
create a BOOLEAN column called superseded with a default
value of false, and create a trigger that sets it to true when
a value is inserted in dt_superseded_after. You can now explicitly
filter on that column to avoid any unexpected NULLishness, and your
queries will read more like English than some constructed inside-
joke language.
Compare:
SELECT * FROM duration WHERE dt_superseded_after IS NULL;
to
SELECT * FROM duration WHERE NOT superseded;
In a larger, more complex query the difference in readability will
be even more marked.
--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/
