Setting default values for columns helps ensure data integrity in your tables. If a default value is set, new rows in the table will be inserted with the default value for that column, regardless of whether it was explicitly specified or not. This can be especially useful when working with null values, as it helps prevent inserting NULL values in your data.

This write will teach you how to add or drop a default value from a table in Postgres using practical examples. In this regard, the following topics will be covered in this blog post:

• Key Points
• How to Add/Set a Default Value to a Column Via CREATE TABLE Statement?
• How to Add/Set a Default Value to a Column Via ALTER TABLE Statement?
• How to Drop the Column’s Default Value Using ALTER TABLE Statement?

So, let’s start!

Key Points

The below-listed points must be kept in mind when setting default values for columns in Postgres:

• In Postgres, the DEFAULT keyword is used with the help of CREATE TABLE or ALTER TABLE statement to set a default value to a column.
• DEFAULT must be a constant expression; it cannot refer to any other column or variable.
• The default value's data type must match the column's data type.
• Default values should be chosen based on your data and use case.

How to Add/Set a Default Value to a Column Via CREATE TABLE Statement?

You can set a default value for a column when you create a Postgres table using the CREATE TABLE statement. Here's the syntax of how you might do this:

CREATE TABLE table_name (
column_name DATA TYPE DEFAULT 'default_value'
);

The above snippet shows that adding a default value to a column can be accomplished via the DEFAULT keyword.

Example: Setting a Default Column Value Via CREATE TABLE Statement

Let’s learn how to add a default value to a column at the time of table creation:

CREATE TABLE product_details(
product_id INT PRIMARY KEY,
product_price INT,
purchase_date DATE DEFAULT CURRENT_DATE
)

The above output shows that the “purchase_date” column has been created with a default value, i.e., “CURRENT_DATE”. Let’s insert a record into the “product_details” table to learn how the DEFAULT keyword works in Postgres:

INSERT INTO product_details(product_id, product_price)
VALUES (1, 2500);

We didn’t specify the value for the “purchase_date” column. However, a default value will be assigned to the “purchase_date” column because of the “DEFAULT” keyword. Let’s run the “SELECT *” command to see the table’s data:

The output authenticates the working of the DEFAULT keyword, as it successfully added a default value to the purchase date column.

How to Add/Set a Default Value to a Column Via ALTER TABLE Statement?

The most common way to add a default value to a column is using the ALTER TABLE statement, which allows you to modify the structure of an existing table. Here's the syntax of how you might use ALTER TABLE statement to set a default value for a column:

ALTER TABLE table_name 
ALTER COLUMN column_name SET DEFAULT 'default_value';

Example: Setting a Default Column Value Via ALTER TABLE Statement

Let’s learn how to set a default value for an existing column using the ALTER TABLE statement:

ALTER TABLE product_details 
ALTER COLUMN product_price SET DEFAULT 0;

A default value has been set for the “product_price” column. Now insert a new record into the product_details table to comprehend the working of the DEFAULT keyword:

INSERT INTO product_details(product_id)
VALUES (2);

Let’s verify the table’s data via the “SELECT *” command:

The output snippet clarifies that a default value has been inserted into the product_price column, proving the DEFAULT keyword's working.

How to Drop the Column’s Default Value Using ALTER TABLE Statement?

If you no longer want to set a default value for a column, then you can use the ALTER TABLE statement to drop the default value:

ALTER TABLE table_name 
ALTER COLUMN column_name DROP DEFAULT;

Example: Dropping a Default Column Value Via ALTER TABLE Statement

If the column’s default value is no longer needed, you can drop it using the ALTER TABLE command as follows:

ALTER TABLE product_details
ALTER COLUMN product_price DROP DEFAULT;

The output snippet shows that the DEFAULT value has been removed from the “product_price” column. Let’s run the INSERT INTO command to insert a new record into the “product_details” table:

INSERT INTO product_details(product_id)
VALUES (3);

Now execute the SELECT * command to see the newly inserted record:

A null value has been inserted for the “product_price” column, it proves that the DEFAULT VALUE feature has been removed from the “product_price” column.

Conclusion

In Postgres, the DEFAULT keyword is used with the help of CREATE TABLE or ALTER TABLE statement to set a default value to a column. DEFAULT must be a constant expression; it cannot refer to any other column or variable. The DEFAULT value for a column is useful when working with null values, as it helps prevent inserting NULL values in your data. This Postgres blog explained several ways to add or remove default values from a column in PostgreSQL.