How to Alter Table
There are several reasons you may want to alter existing table definition such as you want to add more columns to a table, you want the size of existing column bigger etc. PostgreSQL provides you ALTER TABLE statements with various choices to do so . With ALTER TABLE statement you can do following things to existing table:
- Add or remove columns
- Altering the column attributes (data type, size, column name)
- Enable or disable triggers, rule associating to the table
- Rename table
Add and remove column
To add or remove column you use the following query:
ALTER TABLE table_name
ADD [COLUMN] column_name data_type [column_constraint]
ALTER TABLE table_name
DROP [COLUMN] column_name [RESTRICT|CASCADE]
First you specify the table you want to add new column or remove an existing column after the ALTER TABLE statement. The table must be existed otherwise you will get an error. To add a new column you use the ADD COLUMN and then specify the column name with its attributes (data type, length, constraints...). To remove an existing column you use DROP COLUMN followed by column name you want to drop.
When you remove a column which is a part of composite key of database table, you may loss data. Because after removing that column the uniqueness of record is determined by other columns in the composite key without the column you deleted. Therefore it is advisable to make a backup of the table first before you want to alter the table definition.
As an example, if you want to remove the
phone column in the
customer table you can execute the following query:
ALTER TABLE customer
DROP COLUMN phone
If you want to add column
phone to the
customer table again, you can executing the following query:
ALTER TABLE customer
ADD COLUMN phone CHARACTER VARYING(16)
Rename column name
If you want to rename existing column you can use the following query:
ALTER TABLE table_name
RENAME COLUMN column_name TO new_column_name
For example if you want to rename the
phone column of
customer to
telephone you can executing the following query:
ALTER TABLE customer
RENAME COLUMN phone TO telephone
Alter column attributes
With ALTER TABLE statement you can alter column's attributes such as default value, nullable or not, constraints, make it as the primary key. Here are the queries to do so:
ALTER TABLE table_name
ALTER COLUMN column_name [ SET | DROP ] [NOT] NULL
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
ALTER TABLE table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY (col2)
REFERENCES table2 (col2) MATCH FULL
ALTER TABLE table_name
ADD PRIMARY KEY (column_name)
Rename table
To rename a table to another name you can use the following query:
ALTER TABLE table_name
RENAME TO new_table_name
There are several another things you can do with ALTER TABLE statement in PostgreSQL but they are rarely used. You can refer PostgreSQL documentation if you are interested in.