Set or Remove Default Value of a Column in PostgreSQL
To change the default value of column, use
SET DEFAULT or
DROP DEFAULT clause with
ALTER TABLE ALTER COLUMN statement.
ALTER TABLE <table_name> ALTER COLUMN <column_name> [SET DEFAULT <value> | DROP DEFAULT];
Consider that you already have the following
The following sets the default value of the
salary column. So if you insert a record without specifying the value for the
salary column, it will store default value in the
salary column for that row.
ALTER TABLE employee ALTER COLUMN salary SET DEFAULT 0;
The following reflects the changes in the employee table.
Remove Default Value
To remove default value of the column, use ALTER TABLE ALTER COLUMN statement along with DROP DEFAULT clause, as shown below.
ALTER TABLE employee ALTER COLUMN salary DROP DEFAULT;
Set Column Default using pgAdmin
You can assign or change the default value of a column in pgAdmin by right clicking on the table and select 'Properties' in the context menu. This will open a popup where you can add or edit multiple columns definition.
In the popup, go to 'Columns' tab and click on the edit icon againt a column to change the default value, as shown below.
Now, go to the 'Constraints' tab and set or change the default value. Click on the Save button to save the changes.