On Sat, Jan 12, 2019 at 9:01 AM Rich Shepard <rshepard@xxxxxxxxxxxxxxx> 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.
