postgresql 仅为新行添加默认为 NOW() 的时间戳列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38916063/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Add timestamp column with default NOW() for new rows only
提问by Artur
I have a table that has thousands of rows. Since the table wasn't constructed with created_at column initially, there is no way of getting their creation timestamp. It is crucial though to start getting the timestamps for future rows.
我有一个有数千行的表。由于该表最初不是用 created_at 列构造的，因此无法获得它们的创建时间戳。尽管开始获取未来行的时间戳至关重要。
Is there a way I can add a timestamp column with default value NOW() so that it won't populate the values to previous rows but only for the future ones?
有没有办法可以添加一个具有默认值 NOW() 的时间戳列，以便它不会将值填充到前一行，而只会填充未来的行？
If I do the ALTERquery, it populates all rows with timestamp:
如果我执行ALTER查询，它会用时间戳填充所有行：
ALTER TABLE mytable ADD COLUMN created_at TIMESTAMP DEFAULT NOW()
回答by Philip Couling
You need to add the column with a default of null, then alter the column to have default now().
您需要添加具有默认值null的列，然后将该列更改为具有默认值now()。
ALTER TABLE mytable ADD COLUMN created_at TIMESTAMP;
ALTER TABLE mytable ALTER COLUMN created_at SET DEFAULT now();
回答by Renzo
You could add the default rule with the alter table,
您可以使用更改表添加默认规则，
ALTER TABLE mytable ADD COLUMN created_at TIMESTAMP DEFAULT NOW()
then immediately set to null all the current existing rows:
然后立即将所有当前现有行设置为 null：
UPDATE mytable SET created_at = NULL
Then from this point on the DEFAULTwill take effect.
那么从这一点DEFAULT上将生效。
回答by Mustafa Ahmad Fathy
For example, I will create a table called usersas below and give a column named datea default value NOW()
例如，我将创建一个users如下所示的表，并给出一个名为date默认值的列NOW()
create table users_parent (
user_id varchar(50),
full_name varchar(240),
login_id_1 varchar(50),
date timestamp NOT NULL DEFAULT NOW()
);
Thanks
谢谢
回答by Michael Gungaram-Smith
Try something like:-
尝试类似的东西：-
ALTER TABLE table_name ADD CONSTRAINT [DF_table_name_Created]
DEFAULT (getdate()) FOR [created_at];
replacing table_namewith the name of your table.
替换table_name为您的表的名称。
