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

Question

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();

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.

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

minor optimization.

select pg_typeof(now());   --returns: timestamp with time zone. So now include timezone.        

So better with timestamptz.

begin;
ALTER TABLE mytable ADD COLUMN created_at TIMESTAMPTZ;
ALTER TABLE mytable ALTER COLUMN created_at SET DEFAULT now();
commit;