Search code examples
postgresqldefaultalterpostgres-9.6setdefault

What's the difference between default and set default in Postgres?

I am kind of new to Postgres and I'm trying to use the following query in Postgres:

alter table tablename add column col1 enum('r', 'g', 'b') not null set default 'r';

It is giving me an error. When I changed it to default instead of set default, it worked fine.

Just wondering what the reason might be!

Solution

  • As for "why does it work"?

    I suspect that you were using it in one of the following two ways:

    alter table tablename add column col1 text default 'r';

    and 

    alter table tablename alter column col1 set default 'r';

    Note that the former alters the table by adding a column with a default value, while in the latter, the column already exists and you alter the column by setting a default value.

    You can find more information about defaults in create table and alter table