Both ways will work.
Setting the superseded_after to a far off future will work but will have
to set to a real date when it actual does become superseded.
The same is true for nulls It boils down to how you and the users want
to look at the data.
To me to get the all the records that has not been superseded yet is
simple either way.
Select * from some_table where superseded_after IS NULL
or
Select * from some_table where superseded_after > current_date
James B. Byrne wrote:
I am confronting a design decision involving null values and I
cannot seem to discern which way to go. Therefore, I would like
some commentary based on real world experience.
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. On the
other hand, setting some artificially excessive future date seems in
its place seems, to me, to have its own problems.
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?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general