PostgreSQL ALTER TABLE statement

PostgreSQL ALTER TABLE statement is used to add, modify, or clear / delete columns in a table. PostgreSQL ALTER TABLE is also used to rename a table.

Add a column to a table

The syntax for adding a column to a table in PostgreSQL (using ALTER TABLE):

ALTER TABLE table_name
ADD new_column_name column_definition;

• table_name – The name of the table to change.
• new_column_name – Name of the new column added to the table.
• column_definition – Column data type.

Consider an example that shows how to add a column to a PostgreSQL table using the ALTER TABLE statement.
For example:

ALTER TABLE order_details
ADD order_date date;

This PostgreSQL example ALTER TABLE will add a column named order_date to the order_details table. It will be created as a NULL column.

Add multiple columns to the table

The syntax for adding multiple columns to a table in PostgreSQL (using ALTER TABLE):

ALTER TABLE table_name
ADD new_column_name column_definition,
ADD new_column_name column_definition,
…
;

• table_name – The name of the table to change.
• new_column_name – The name of the new column added to the table.
• column_definition – Column data type.

Consider an example that shows how to add multiple columns to a PostgreSQL table using the ALTER TABLE statement.
For example:

ALTER TABLE order_details
ADD order_date date,
ADD quantity integer;

This example will add two columns to the order_details table – order_date and quantity.

The order_date field will be created as a column with a date data type, and the quantity column will be created as a column with an integer data type.

Change the column in the table

Syntax to change the column in the table in PostgreSQL (using ALTER TABLE):

ALTER TABLE table_name
ALTER COLUMN column_name TYPE column_definition;

• table_name – The name of the table to change.
• column_name – The name of the column to be changed in the table.
• column_definition – The changed column data type.

Consider an example that shows how to change 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 change the column named notes to varchar (500) data type in the order_details table.

Change multiple columns in the table

Syntax to change multiple columns in a table in PostgreSQL (using ALTER TABLE):

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 change.
• column_name – The name of the column to be changed in the table.
• column_definition – The changed type of column data.

Consider an example that shows how to change 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;

In this ALTER TABLE example, two columns of the order_details table – notes and quantity – will be changed.

The notes field will be changed to varchar(500) data type, and the quantity column will be changed to the numeric data type.

Delete the column in the table

Syntax to delete a column in a table in PostgreSQL (using ALTER TABLE operator):

ALTER TABLE table_name
DROP COLUMN column_name;

• table_name – The name of the table to change.
• column_name – The name of the column to be removed from the table.

Consider an example that shows how to remove 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 delete the column named notes from the table named order_details.

Rename the column in the table

Syntax to rename the column in the table to PostgreSQL (using ALTER TABLE operator):

ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;

• table_name – Name of the table to change.
• old_name – Column to be renamed.
• new_name – The new name for the column.

Consider an example that shows how to rename a column in a PostgreSQL table using the ALTER TABLE operator.
For example:

ALTER TABLE order_details
RENAME COLUMN notes TO order_notes;

This PostgreSQL example ALTER TABLE will rename the column with the name notes to order_notes in the order_details table.

Rename the table

The syntax for renaming a table to PostgreSQL (using ALTER TABLE operator):

ALTER TABLE table_name
RENAME TO new_table_name;

• table_name – A table for renaming.
• new_table_name – The new name of the table.

Consider an example that shows how to rename a table to PostgreSQL using the ALTER TABLE operator.
For example:

ALTER TABLE order_details
RENAME TO order_information;

This example ALTER TABLE will rename the order_details table to order_information.

PostgreSQL Tutorial for Beginners – PostgreSQL ALTER TABLE