Hi,
In MYSQL Null date is equal '01/01/1900' or '01/01/1970', I don't remember but you can also use the same logic for the check_constraint.
Regards
Obtener Outlook para Android
From: Rich Shepard <rshepard@xxxxxxxxxxxxxxx>
Sent: Saturday, January 12, 2019 1:54:47 PM
To: pgsql-general@xxxxxxxxxxxxxxxxxxxx
Subject: Re: insert into: NULL in date column
Sent: Saturday, January 12, 2019 1:54:47 PM
To: pgsql-general@xxxxxxxxxxxxxxxxxxxx
Subject: Re: insert into: NULL in date column
On Sat, 12 Jan 2019, David G. Johnston wrote:
> 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>"
David,
Thanks for correcting me.
> 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.
I thought that the check constraint applied at data entry, too. If not,
then I'll have either wxPython or SQLAlchemy ensure that the next_contact
date is later than the contact date.
> 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.
The contact date is always entered in a new row, but the next_contact date
might not be if there's nothing scheduled.
Best regards,
Rich
> 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>"
David,
Thanks for correcting me.
> 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.
I thought that the check constraint applied at data entry, too. If not,
then I'll have either wxPython or SQLAlchemy ensure that the next_contact
date is later than the contact date.
> 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.
The contact date is always entered in a new row, but the next_contact date
might not be if there's nothing scheduled.
Best regards,
Rich
