PostgreSQL: ALTER TABLE Statement

In this PostgreSQL post 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.