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 a date data type column, whereas the quantity column will create as an integer data type column.

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 table column has to be modified.
• 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 altered.

We will change the data type of the notes field to varchar (500) and change the quantity column’s data type to numeric.

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 – Will eliminate the name of the table column.

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 – We will rename this column.
• 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

About Enteros

IT organizations routinely spend days and weeks troubleshooting production database performance issues across multitudes of critical business systems. Fast and reliable resolution of database performance problems by Enteros enables businesses to generate and save millions of direct revenue, minimize waste of employees’ productivity, reduce the number of licenses, servers, and cloud resources and maximize the productivity of the application, database, and IT operations teams.