PostgreSQL Add Column

PostgreSQL Add Column

Databases naturally evolve alongside the applications that aggressively rely on them. You will frequently need to explicitly add new columns to an existing database table.

To cleanly accomplish this, PostgreSQL uses the ALTER TABLE command seamlessly. It allows you to deeply modify a table's strict architecture without losing existing data.

The ADD COLUMN Syntax

The syntax rigorously requires you to specify the targeted table you wish to alter. You then utilize the ADD COLUMN keywords followed by the new column's name and type.

You can seamlessly assign constraints, like making the new column universally unique. The operation is fast, but adding defaults to massive tables can sometimes take time.

ALTER TABLE table_name
ADD COLUMN new_column_name datatype constraint;

Adding a Simple Column

Let's dynamically add a new column to explicitly track a user's phone number. We will use the VARCHAR data type to safely allow dashes and parenthesis.

Once executed, every single existing row will magically receive this new column. The value for this new column on all older, existing rows will simply default to NULL.

-- Add a phone number column to the Users table
ALTER TABLE Users
ADD COLUMN PhoneNumber VARCHAR(20);

Adding Multiple Columns

You do not have to write separate ALTER TABLE statements for every individual addition. PostgreSQL gracefully allows you to add multiple columns in one single, fluid command.

You simply separate the new ADD COLUMN definitions with a standard comma natively. This significantly cleans up your database migration scripts and deployment files.

-- Add both Age and Status columns simultaneously
ALTER TABLE Users
ADD COLUMN Age INTEGER,
ADD COLUMN Status VARCHAR(15);

Adding Columns with Defaults

Sometimes you do not want older rows to natively default to an empty NULL value. You can seamlessly append the DEFAULT constraint entirely during the addition process.

PostgreSQL will actively populate the brand new column with this default value instantly. This forcefully ensures strict data consistency across both old and entirely new records.

-- Add a boolean column and default all existing users to TRUE
ALTER TABLE Users
ADD COLUMN IsActive BOOLEAN DEFAULT TRUE;

Summary

The ALTER TABLE ADD COLUMN command allows safe, non-destructive schema evolution. Older rows will safely receive a NULL value unless a default is proactively specified.

Always test table alterations vigorously in a safe development environment first.