PostgreSQL allows us to add the default values to the table’s column. To do that, the DEFAULT keyword is used in Postgres during table creation or alteration. Setting the columns’ default values provides an ease to the users; however, they can sometimes be annoying. For example, while customizing your data or if they're no longer needed. Thankfully, dropping a default value from a column is a straightforward process that can be accomplished using the “ALTER TABLE DROP DEFAULT” command.

This blog will present a detailed overview of adding or dropping a default value from a column using the ALTER TABLE command in Postgres. So, let’s get started.

Setting/Adding a Column’s Default Value in Postgres

We have already created a “product_info” table, whose structure is shown in the following snippet:

The output snippet shows that the default value is not added to any column. Let’s set today’s date as the default value of the purchase_date column:

ALTER TABLE product_info
ALTER COLUMN purchase_date SET DEFAULT NOW();

Let’s describe the “product_info” table to check the default value:

The output shows that the default value has been added to the “purchase_date” column.

Dropping a Default Value From a Column in Postgres

To drop/remove the default value from a column, you need to use the “DROP DEFAULT” keyword with the assistance of the “ALTER TABLE” command:

ALTER TABLE product_info
ALTER COLUMN purchase_date DROP DEFAULT;

The output snippet shows that the “ALTER TABLE DROP DEFAULT” command was executed successfully. Let’s describe the “product_info” table one more time to check the default value of the purchase date column:

The above image shows that the default value has been dropped successfully.

This way, you can add/drop the column’s default value using the ALTER TABLE command in Postgres.

Conclusion

PostgreSQL allows us to add or drop the default values to the table’s column using the ALTER TABLE command. To drop/remove the default value from a column, you need to use the “DROP DEFAULT” keyword with the assistance of the “ALTER TABLE” command. This blog considered multiple examples of adding or dropping the column’s default value using the ALTER TABLE command in Postgres.