PostgreSQL Alter Table

The ALTER TABLE Statement

ALTER TABLE modifies a table's definition rather than its data. With it you can add a new column, drop a column you no longer need, rename a column or the table itself, or change a column's data type. The general form is ALTER TABLE table_name followed by one or more actions, such as ADD COLUMN, DROP COLUMN, or RENAME COLUMN.

ALTER TABLE employees
ADD COLUMN phone VARCHAR(20);

New columns often need a default value and a NOT NULL constraint so existing rows aren't left blank. PostgreSQL fills in the default for every existing row as part of the same statement, and any future INSERT that omits the column uses that default automatically.

ALTER TABLE employees
ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true;

Dropping and Renaming Columns

DROP COLUMN removes a column and all of its data permanently. RENAME COLUMN changes a column's name without touching its data or type, which is useful when a name no longer reflects what it stores. You can also rename the entire table with ALTER TABLE ... RENAME TO.

ALTER TABLE employees
DROP COLUMN phone;

ALTER TABLE employees
RENAME COLUMN department TO dept_name;

• ADD COLUMN name TYPE - add a new column, optionally with DEFAULT and NOT NULL.
• DROP COLUMN name - permanently remove a column and its data.
• RENAME COLUMN old TO new - rename a column without affecting its data.
• RENAME TO new_name - rename the table itself.
• ALTER COLUMN name TYPE new_type - change a column's data type.