How do I an add a DEFAULT on a column if the table already exists?

Question

I want to add a DEFAULT for a timestamp column in PostgreSQL. With SQL Server you use ALTER TABLE ADD CONSTRAINT

You can see the example in their docs,

ALTER TABLE dbo.doc_exz  
ADD CONSTRAINT col_b_def  
DEFAULT 50 FOR column_b;  

In my case, I'm trying the same pattern,

ALTER TABLE foo
ADD CONSTRAINT column_name
  DEFAULT now() FOR bar;

This doesn't work. What is the proper way to add a DEFAULT for a column in PostgreSQL?

Answer 1

In PostgreSQL, DEFAULT is not a constraint. PostgreSQL follows the spec and implements the <set column default clause>. See the ALTER TABLE command documentation for more details. Your command should look like this:

ALTER TABLE foo
  ALTER COLUMN column_name
  SET DEFAULT now();