ALTER TABLE Statement
This PostgreSQL tutorial explains how to use the PostgreSQL ALTER TABLE statement to add a column, modify a column, drop a column, rename a column or rename a table (with syntax and examples).
Description
The PostgreSQL ALTER TABLE statement is used to add, modify, or drop/delete columns in a table. The PostgreSQL ALTER TABLE statement is also used to rename a table.
Add column in table
Syntax
The syntax to add a column in a table in PostgreSQL (using the ALTER TABLE statement) is:
ALTER TABLE table_name
ADD new_column_name column_definition;
table_name
The name of the table to modify.
new_column_name
The name of the new column to add to the table.
column_definition
The datatype of the column.
Example
Let's look at an example that shows how to add a column in a PostgreSQL table using the ALTER TABLE statement.
For example:
ALTER TABLE order_details
ADD order_date date;
This PostgreSQL ALTER TABLE example will add a column called order_date to the order_details table. It will be created as a NULL column.
Add multiple columns in table
Syntax
The syntax to add multiple columns in a table in PostgreSQL (using the ALTER TABLE statement) is:
ALTER TABLE table_name
ADD new_column_name column_definition,
ADD new_column_name column_definition,
...
;
table_name
The name of the table to modify.
new_column_name
The name of the new column to add to the table.
column_definition
The datatype of the column.
Example
Let's look at an example that shows how to add multiple columns in a PostgreSQL table using the ALTER TABLE statement.
For example:
ALTER TABLE order_details
ADD order_date date,
ADD quantity integer;
This ALTER TABLE example will add two columns to the order_details table - order_date and quantity.
The order_date field will be created as a date column and the quantity column will be created as an integer column.
Modify column in table
Syntax
The syntax to modify a column in a table in PostgreSQL (using the ALTER TABLE statement) is:
ALTER TABLE table_name
ALTER COLUMN column_name TYPE column_definition;
table_name
The name of the table to modify.
column_name
The name of the column to modify in the table.
column_definition
The modified datatype of the column.
Example
Let's look at an example that shows how to modify a column in a PostgreSQL table using the ALTER TABLE statement.
For example:
ALTER TABLE order_details
ALTER COLUMN notes TYPE varchar(500);
This ALTER TABLE example will modify the column called notes to be a data type of varchar(500) in the order_details table.
Modify Multiple columns in table
SyntaX
The syntax to modify multiple columns in a table in PostgreSQL (using the ALTER TABLE statement) is:
ALTER TABLE table_name
ALTER COLUMN column_name TYPE column_definition,
ALTER COLUMN column_name TYPE column_definition,
...
;
table_name
The name of the table to modify.
column_name
The name of the column to modify in the table.
column_definition
The modified datatype of the column.
Example
Let's look at an example that shows how to modify multiple columns in a PostgreSQL table using the ALTER TABLE statement.
For example:
ALTER TABLE order_details
ALTER COLUMN notes TYPE varchar(500),
ALTER COLUMN quantity TYPE numeric;
This ALTER TABLE example will modify two columns to the order_details table - notes and quantity.
The notes field will be changed to a varchar(500) column and the quantity column will be modified to a numeric column.
Drop column in table
Syntax
The syntax to drop a column in a table in PostgreSQL (using the ALTER TABLE statement) is:
ALTER TABLE table_name
DROP COLUMN column_name;
table_name
The name of the table to modify.
column_name
The name of the column to delete from the table.
Example
Let's look at an example that shows how to drop a column in a PostgreSQL table using the ALTER TABLE statement.
For example:
ALTER TABLE order_details
DROP COLUMN notes;
This ALTER TABLE example will drop the column called notes from the table called order_details.
Rename column in table
Syntax
The syntax to rename a column in a table in PostgreSQL (using the ALTER TABLE statement) is:
ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;
table_name
The name of the table to modify.
old_name
The column to rename.
new_name
The new name for the column.
Example
Let's look at an example that shows how to rename a column in a PostgreSQL table using the ALTER TABLE statement.
For example:
ALTER TABLE order_details
RENAME COLUMN notes TO order_notes;
This PostgreSQL ALTER TABLE example will rename the column called notes to order_notes in the order_details table.
Rename table
Syntax
To rename a table, the PostgreSQL ALTER TABLE syntax is:
ALTER TABLE table_name
RENAME TO new_table_name;
table_name
The table to rename.
new_table_name
The new table name.
Example
Let's look at an example that shows how to rename a table in PostgreSQL using the ALTER TABLE statement.
For example:
ALTER TABLE order_details
RENAME TO order_information;
This ALTER TABLE example will rename the order_details table to order_information.