CODEWITHSUNDEEP
postgresqldatetimetime
Andrew Graham-Yooll
Andrew Graham-Yooll

Reputation: 2258

Creating a default timestamp in PostgreSQL

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"

Upvotes: 0

Views: 4730

Answers (1)

Andrew Graham-Yooll
Andrew Graham-Yooll

Reputation: 2258

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.

Upvotes: 1

Related Questions