Adding a new SQL column with a default value

sql

database-management

sql-queries

data-types

byAlex Kataev·Jan 13, 2025

To add a column with a default value, this is your syntax:

ALTER TABLE table_name
ADD COLUMN column_name datatype DEFAULT 'value';

Here, replace table_name, column_name, datatype, and 'value' with your specifics. This statement will add the column_name to your table_name, sets its type, and populates it with 'value' for existing rows.

Details on ALTER TABLE with defaults

When modifying a table, providing the exact data type and default value is critical. Here's an example:

-- Adding "start_date" column with "current date" as default, takes 'no excuses' policy seriously!
ALTER TABLE employee ADD COLUMN start_date DATE NOT NULL DEFAULT CURRENT_DATE;

Using NOT NULL with DEFAULT

Combining NOT NULL and DEFAULT ensures the column always houses a value:

-- Avoid stock-outs! Add "quantity_in_stock" with 0 as default to all products!
ALTER TABLE product ADD COLUMN quantity_in_stock INT NOT NULL DEFAULT 0;

This approach prevents confusion with NULL values, representing unknown quantities in your inventory.

Handling diverse data types

Your default may vary depending on the data type. Numeric types often default to 0, while a specific text or an empty string suits string types.

-- Activate new users by default, because 'sign-up fatigue' is too real!
ALTER TABLE user ADD COLUMN is_active TINYINT(1) NOT NULL DEFAULT 1;

Transforming tables: Advanced scenarios & considerations

Integrating expressions as defaults

In some cases, you might set an expression or a function as a default value:

-- Track time like a pro! Add a timestamp to log entries.
ALTER TABLE log ADD COLUMN timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

Modifying defaults on demand

To change the default value later, use:

-- Travel back in time! Set default for customers' last purchase to a fixed date.
ALTER TABLE customer ALTER COLUMN last_purchase SET DEFAULT '2000-01-01';

Adapt to your SQL dialect

SQL dialects can be subtly different, make sure to adapt correctly:

-- PostgreSQL users, keep it simple:
ALTER TABLE team ADD COLUMN points SMALLINT NOT NULL DEFAULT 0;

-- SQL Server folks, don't forget the double brackets!
ALTER TABLE team ADD points SMALLINT NOT NULL DEFAULT ((0));