Creating a default timestamp in PostgreSQL

Question

I want to create default timestamp column in an existing table.

My first query is

"ALTER TABLE {table_name} ADD COLUMN modifiedDate timestamp without time zone"

This works and successfully adds the column.

However the second query

ALTER TABLE {table_name} ALTER modifiedDate SET DEFAULT '2001-01-01 00:00:00'::timestamp without time zone;

fails to update all the rows on the column to contain that timestamp.

I was following this SO post.

I also tried in one query

"ALTER TABLE {table_name} ADD COLUMN modifiedDate timestamp without time zone" SET DEFAULT '2001-01-01 00:00:00'::timestamp without time zone;

but that gave an error syntax error at or near "00"

Andrew Graham-Yooll · Accepted Answer · 2017-08-06 08:56:59Z

1

Silly mistake.

ALTER TABLE {table_name} ADD COLUMN modifiedDate timestamp without time zone NOT NULL DEFAULT '2001-01-01 00:00:00'::timestamp without time zone;

The SET should not be in the one liner.

answered Aug 6, 2017 at 8:56

2,2784 gold badges27 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1