PostgreSQL ALTER TABLE Command

In PostgreSQL,ALTER TABLEthe command is used to add, modify, and delete columns of an existing table.

Additionally, you can use theALTER TABLEcommand to add and drop 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 COLUMN (delete a column) on an existing table, the syntax is as follows:

ALTER TABLE table_name DROP COLUMN column_name;

To modify the DATA TYPE of a column in a table, the syntax is as follows:

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 as follows:

ALTER TABLE table_name ALTER column_name datatype NOT NULL;

To ADD a UNIQUE CONSTRAINT to a column in a table, the syntax is as follows:

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

To ADD a CHECK CONSTRAINT to a table, the syntax is as follows:

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

To ADD a PRIMARY KEY to a table, the syntax is as follows:

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

DROP CONSTRAINT (delete a constraint), the syntax is as follows:

ALTER TABLE table_name
DROP CONSTRAINT MyUniqueConstraint;

If it is MYSQL, the code is like this:

ALTER TABLE table_name
DROP INDEX MyUniqueConstraint;

DROP PRIMARY KEY (delete the primary key), the syntax is as follows:

ALTER TABLE table_name
DROP CONSTRAINT MyPrimaryKey;

If it is MYSQL, the code is like this:

ALTER TABLE table_name
DROP PRIMARY KEY;

Examples

Create the COMPANY table (Download the COMPANY SQL file), the data content is as follows:

exampledb# 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:

exampledb=# 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 drops the GENDER column:

exampledb=# 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

Other Extensions