How to Add a Default Value in PostgreSQL

Adding a default value to a column in PostgreSQL ensures that when no value is provided during an insert, the column automatically gets the default.

Why Use a Default Value?

Default values help keep your data consistent and reduce the need for client-side handling. For example, you might want a status column to default to 'active' or a created_at column to default to the current timestamp.

Basic Syntax

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT default_value;

Example 1: Add a Default String Value

ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';

This sets the status column of the users table to default to 'active' if no value is provided.

Example 2: Add a Default Numeric Value

ALTER TABLE orders ALTER COLUMN quantity SET DEFAULT 1;

Example 3: Add a Default Timestamp

ALTER TABLE posts ALTER COLUMN created_at SET DEFAULT now();

Important Tips

• Setting a default does not update existing rows. Use UPDATE if you need to set a value for old data.
• To remove a default value, use ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;
• Test on a development or staging environment before applying changes in production.

Update Existing Rows (Optional)

If you want to update old rows to match the new default:

UPDATE users SET status = 'active' WHERE status IS NULL;

Conclusion

Adding default values in PostgreSQL is a simple but powerful tool for improving data quality and simplifying application code. Remember to combine it with updates for existing rows if necessary.