PostgreSQL: 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.
Advertisements