CODEWITHSUNDEEP
postgresql
Artur
Artur

Reputation: 2229

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

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

Upvotes: 216

Views: 337180

Answers (5)

jian
jian

Reputation: 4877

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;

Upvotes: 5

Mustafa Ahmad Fathy
Mustafa Ahmad Fathy

Reputation: 593

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

Upvotes: 49

Michael Gungaram-Smith
Michael Gungaram-Smith

Reputation: 7

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.

Upvotes: -3

Philip Couling
Philip Couling

Reputation: 14932

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

Upvotes: 286

Renzo
Renzo

Reputation: 27434

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.

Upvotes: 78

Related Questions