I have a Postgres table with certain data, let's say I have 3 columns at the beginning:

Later on I want to add a new field created_date to record when a user is created and meet 2 sceanrios:

1. For the existing users, leave the fields empty
2. 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?

CodePudding user response：

There are only three options:

1. Make the new column not nullable
2. Make the column nullable and use a default date for all existing entries like 01.01.2000. You can set a default value on the column or do an update after adding the column. In the second case the not null needs to be added (with alter table statement) to the column after the update statement.
3. Create a complete new Table and use it to insert new entries. To read all values together (old entries without date column and new columns with date column) you can make a View which combines the two tables with a union all. This case requires adjustments in your Application and a good thinking about to not have duplicate entries in both tables. And of course the sequences needs to be adjusted aswell. I would not go this way.

Unfortunately there is no other option if the column needs to be not null.

CodePudding user response：

I'd recommend instead:

ALTER TABLE users
    ADD COLUMN created TIMESTAMP WITH TIME ZONE NOT NULL
        DEFAULT '1970-01-01 00:00:00 UTC';
ALTER TABLE users
    ALTER COLUMN created SET DEFAULT now();

That's because:

• The column name "current_date" is misleading. This is a timestamp, not just a date.
• You should always use "timestamp with time zone" for timestamps, or you'll otherwise have various bugs, like values going backwards, being duplicated, jumping forward, being interpreted differently depending on the client's time zone etc.
• This will fill currently existing rows with the timestamp '1970-01-01 00:00:00 UTC', which is immediately recognized as a timestamp "0", so called "epoch time", making it obviously fake, but still older than any newly created.
• After that, changing the default to 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.