Add timestamp column with default NOW for new rows only

Question

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    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   If I do the ALTER query  it populates all rows with timestamp   ALTER TABLE mytable ADD COLUMN created at TIMESTAMP DEFAULT NOW

User · Answer

You need to add the column with a default of null  then alter the column to have default now     ALTER TABLE mytable ADD COLUMN created at TIMESTAMP  ALTER TABLE mytable ALTER COLUMN created at SET DEFAULT now

User · Answer

For example  I will create a table called users as below and give a column named date a default value 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

User · Answer

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   UPDATE mytable SET created at   NULL   Then from this point on the DEFAULT will take effect

User · Answer

Try something like -  ALTER TABLE table name ADD  CONSTRAINT  DF table name Created   DEFAULT  getdate    FOR  created at     replacing table name with the name of your table

[postgresql] Add timestamp column with default NOW() for new rows only

Examples related to postgresql