WikiGalaxy
PostgreSQL ALTER TABLE
Overview:
The ALTER TABLE command in PostgreSQL is used to modify the structure of an existing table. It allows you to add, delete, or modify columns, change data types, rename columns, and more.
Adding a New Column
Use Case:
To add a new column to an existing table, you can use the ADD COLUMN clause. This is useful when you need to store additional information in your table.
ALTER TABLE employees ADD COLUMN birthdate DATE;
Dropping a Column
Use Case:
If a column is no longer needed, it can be removed using the DROP COLUMN clause. This helps in cleaning up unused data fields.
ALTER TABLE employees DROP COLUMN birthdate;
Renaming a Column
Use Case:
To rename a column for clarity or consistency, the RENAME COLUMN clause is used. This is beneficial for maintaining clear and understandable database schemas.
ALTER TABLE employees RENAME COLUMN birthdate TO date_of_birth;
Changing Data Type
ALTER TABLE employees ALTER COLUMN salary TYPE DECIMAL(10, 2);
Setting a Default Value
Use Case:
The SET DEFAULT clause is used to set a default value for a column, ensuring consistent data entry.
ALTER TABLE employees ALTER COLUMN status SET DEFAULT 'active';
Removing a Default Value
Use Case:
To remove a default value from a column, the DROP DEFAULT clause is used. This is helpful when the default value is no longer applicable.
ALTER TABLE employees ALTER COLUMN status DROP DEFAULT;
Renaming a Table
Use Case:
To rename an entire table, the RENAME TO clause is used. This is useful for rebranding or restructuring your database.
ALTER TABLE employees RENAME TO staff;
Adding a Constraint
Use Case:
Constraints ensure data integrity. The ADD CONSTRAINT clause can be used to add constraints like unique, primary key, etc.
ALTER TABLE employees ADD CONSTRAINT unique_email UNIQUE (email);
Dropping a Constraint
Use Case:
To remove a constraint that is no longer needed, use the DROP CONSTRAINT clause. This is essential for flexibility in database management.
ALTER TABLE employees DROP CONSTRAINT unique_email;
