Postgresql – How to an add a DEFAULT on a column if the table already exists

alter-tabledefault valuepostgresql

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?

Best Answer

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