Prev Next

PostgreSQL ADD COLUMN

When you need to modify the structure of an existing table by adding a new column, use the ALTER TABLE ... ADD COLUMN statement. This is common when your data model evolves.

Key Topics

1. ADD COLUMN Syntax

ALTER TABLE table_name
ADD COLUMN new_column_name data_type;

2. Setting Default Values

You can specify a default value for the new column:

ALTER TABLE employees
ADD COLUMN department VARCHAR(50) DEFAULT 'General';

All existing rows receive the default value if no other value is provided.

3. Adding Constraints

You can also add constraints while adding a column:

ALTER TABLE employees
ADD COLUMN start_date DATE NOT NULL;

Here, the NOT NULL constraint ensures that no null values are allowed for start_date.

Best Practices

  • Always consider the impact of adding a column on existing data and queries.
  • Specify default values for new columns to avoid null issues.
  • Use constraints to maintain data integrity at the database level.

Key Takeaways

  • ALTER TABLE ... ADD COLUMN is used to modify table structures.
  • Default values ensure consistent data when adding new columns.
  • Constraints help enforce data quality even as schemas evolve.
Prev Next