How to Add a Column in PostgreSQL

In this tutorial, you’ll learn how to add a new column to an existing PostgreSQL table using the ALTER TABLE command. This is a fundamental skill when modifying database schemas without losing data.

Step 1: Understand the ALTER TABLE Command

The basic syntax to add a column in PostgreSQL is:

ALTER TABLE table_name ADD COLUMN column_name data_type;

For example, if you want to add a birthdate column of type DATE to a table called users, you would write:

ALTER TABLE users ADD COLUMN birthdate DATE;

Step 2: Add a Column with a Default Value

If you want the new column to have a default value, use:

ALTER TABLE users ADD COLUMN active BOOLEAN DEFAULT TRUE;

Step 3: Add a Column with NOT NULL Constraint

To add a column that must always have a value (non-nullable), provide a default or set values after adding the column:

ALTER TABLE users ADD COLUMN country VARCHAR(50) NOT NULL DEFAULT 'USA';

Step 4: Verify Your Changes

You can check the table structure using the \d command in psql:

\d users

Or query the information_schema:

SELECT column_name, data_type 
FROM information_schema.columns 
WHERE table_name = 'users';

Best Practices

• Always back up your database before altering tables.
• Use transactions when applying multiple schema changes.
• Test on a staging environment before running on production.

Summary

Adding a column in PostgreSQL is simple with the ALTER TABLE ... ADD COLUMN command. By understanding the syntax and options like default values and constraints, you can confidently evolve your database schema.