I have a Postgres table with certain data, let’s say I have 3 columns at the beginning:
name | age | gender |
---|---|---|
Name1 | 31 | F |
Name2 | 18 | M |
Name3 | 22 | F |
Later on I want to add a new field created_date
to record when a user is created and meet 2 sceanrios:
- For the existing users, leave the fields empty
- For the new users, the field
created_date
is required and can’t be NULL.
Now I can’t find a way to how to define "empty" since it can’t be null if I add created_date
NOT NULL
like below query, but the same time I don’t want to add DEFAULT xxx
since the time is inaccurate.
ALTER TABLE `users`
ADD `created_Date` DATETIME NOT NULL
DEFAULT '2023-02-03 00:00:00'
Can anyone help to define the "empty" in this case?
2
Answers
There are only three options:
Unfortunately there is no other option if the column needs to be not null.
I’d recommend instead:
That’s because:
now()
will make new rows fill the timestamp automatically and correctly, when the client will either skip the column or will use DEFAULT as value.