On Sat, Jan 12, 2019 at 9:01 AM Rich Shepard <rshepard@appl-ecosys.com> wrote:
>
> On Sat, 12 Jan 2019, David G. Johnston wrote:
>
> > Actually, you didn't ask about the check constraint, which is actually
> > horribly broken since current_date is not an immutable function.
>>
> I know that nulls cannot be validly used in comparisons which makes the
> check constraint FUBAR.
NULL isn't the problem - a check constraint can resolve to unknown in
which case it behaves the same as if it resolved as true (i.e., its
basically a <check> IS NOT FALSE test in the backend). This is
actually a nice feature of check constraints since for nullable
columns you don't have to write "col IS NULL OR <the check I really
care about>"
The problem is that check constraints are only applied at time of data
change. If you insert a record whose date is 3 days from now the
check constraint passes today and (in theory) for the next couple of
days. After which the constraint fails - but you are INFORMED ONLY IF
THE RECORD IS INSERTED AGAIN. So basically you will not see a problem
until you attempt to restore your data on some future date and much of
your data fails to restore because those dates are no longer in the
future.
If you want to check for a future date you should probably also store
the date you are comparing against and have the check constraint
reference both fields.
David J.