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 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

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

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