PostgreSQL ALTER TABLE Command

In PostgreSQL, the ALTER TABLE command is used to add, modify, and delete columns in an existing table.

Additionally, you can also use the ALTER TABLE command to add and remove constraints.

Syntax

The syntax for adding a column to an existing table using ALTER TABLE is as follows:

ALTER TABLE table_name ADD column_name datatype;

To drop a column (delete a column) in an existing table, the syntax is:

ALTER TABLE table_name DROP COLUMN column_name;

To modify the data type of a column in a table, the syntax is:

ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;

To add a NOT NULL constraint to a column in a table, the syntax is:

ALTER TABLE table_name ALTER column_name SET NOT NULL;

To add a UNIQUE constraint to a column in a table, the syntax is:

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);

To add a CHECK constraint to a table, the syntax is:

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

To add a PRIMARY KEY to a table, the syntax is:

ALTER TABLE table_name
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);

To drop a constraint, the syntax is:

ALTER TABLE table_name
DROP CONSTRAINT MyUniqueConstraint;

For MySQL, the code would be:

ALTER TABLE table_name
DROP INDEX MyUniqueConstraint;

To drop a PRIMARY KEY, the syntax is:

ALTER TABLE table_name
DROP CONSTRAINT MyPrimaryKey;

For MySQL, the code would be:

ALTER TABLE table_name
DROP PRIMARY KEY;

Example

Create a COMPANY table (download COMPANY SQL file), with the following data content:

tutorialprodb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

The following example adds a new column to this table:

tutorialprodb=# ALTER TABLE COMPANY ADD GENDER char(1);

Now the table looks like this:

id | name  | age | address     | salary | gender
----+-------+-----+-------------+--------+--------
  1 | Paul  |  32 | California  |  20000 |
  2 | Allen |  25 | Texas       |  15000 |
  3 | Teddy |  23 | Norway      |  20000 |

4 | Mark  | 25 | Rich-Mond | 65000 |
5 | David | 27 | Texas     | 85000 |
6 | Kim   | 22 | South-Hall| 45000 |
7 | James | 24 | Houston   | 10000 |
(7 rows)

The following example deletes the GENDER column:

tutorialprodb=# ALTER TABLE COMPANY DROP GENDER;

The result is as follows:

id | name  | age | address   | salary
----+-------+-----+-----------+--------
1 | Paul  | 32 | California| 20000
2 | Allen | 25 | Texas     | 15000
3 | Teddy | 23 | Norway    | 20000
4 | Mark  | 25 | Rich-Mond | 65000
5 | David | 27 | Texas     | 85000
6 | Kim   | 22 | South-Hall| 45000
7 | James | 24 | Houston   | 10000