How to Remove a Default Value From a Column in PostgreSQL

PostgreSQL allows you to define default values for columns, which can help simplify data entry. But what if you need to remove an existing default? This tutorial walks you through the steps to safely remove a default value from a PostgreSQL column.

Why Remove a Default Value?

There are several reasons you might want to remove a default value:

• The default is no longer valid or relevant.
• Application logic has changed and defaults are now handled elsewhere.
• You want to enforce that values are always provided explicitly.

Step 1: Check the Current Default

Before making any changes, it’s good practice to check if the column has a default. You can do this by running the following query:

SELECT column_name, column_default 
FROM information_schema.columns 
WHERE table_name = 'your_table' AND column_name = 'your_column';

Step 2: Remove the Default Value

Use the ALTER TABLE command with ALTER COLUMN ... DROP DEFAULT to remove the default value:

ALTER TABLE your_table 
ALTER COLUMN your_column DROP DEFAULT;

Example:

ALTER TABLE users 
ALTER COLUMN status DROP DEFAULT;

Step 3: Verify the Change

After running the command, re-check the column definition:

SELECT column_name, column_default 
FROM information_schema.columns 
WHERE table_name = 'users' AND column_name = 'status';

You should see that column_default is now NULL.

Additional Tips

• Make sure no application logic depends on the default before removing it.
• Test the change in a development or staging environment before applying it in production.
• Consider adding NOT NULL constraints if you want to enforce that a value is always provided.

Summary

Removing a default value in PostgreSQL is straightforward using the ALTER TABLE ... ALTER COLUMN ... DROP DEFAULT command. Always remember to check the current schema, communicate with your team, and test thoroughly to avoid unexpected issues.

If you found this tutorial helpful, check out our other PostgreSQL tutorials for more tips and best practices!