The ALTER TABLE command in PostgreSQL lets you add or modify columns in database tables.

What is PostgreSQL’s ALTER TABLE?

The ALTER TABLE command in PostgreSQL can be used to modify tables in a database. This command lets you add, remove or adjust columns in a table as needed. It can also be used to implement or lift constraints on a table in the database management system. To use this command, you need to combine it with a specific action.

Dedicated Server
Performance through innovation
  • Enterprise hardware
  • Power and flexibility
  • Latest security technology

What is the syntax for ALTER TABLE?

To get a better understanding of how to use ALTER TABLE, let’s first take a look at its syntax:

ALTER TABLE table_name action;
postgresql

After the command, you need to specify the name of the table you want to modify and choose the corresponding action for the changes you want to make.

Tip

To create a new table, you can use the CREATE TABLE command in PostgreSQL.

PostgreSQL ALTER TABLE examples

Below, we’ll demonstrate how ALTER TABLE works with a simple example. We’ll use a table named customers that has three columns and three rows:

id name city
1 Lee Northampton
2 Johnson Edinburgh
3 Smith Brighton

We can adjust this table in various ways using ALTER TABLE.

Adding a column with PostgreSQL ADD COLUMN

To add a new column, use ALTER TABLE in combination with PostgreSQL’s ADD COLUMN action. This action requires two parameters: the name of the new column and its data type. The syntax is as follows:

ALTER TABLE table_name ADD COLUMN column_name data_type;
postgresql

For example, here’s how you can add an address column to the customers table:

ALTER TABLE customers ADD COLUMN address VARCHAR(255);
postgresql

Here’s what the table looks like now:

id name city address
1 Lee Northampton NULL
2 Johnson Edinburgh NULL
3 Smith Brighton NULL

Removing a column with DROP COLUMN

To remove a column from a table, use ALTER TABLE with the DROP COLUMN action. Here, you only need to add the column name as a parameter:

ALTER TABLE table_name DROP COLUMN column_name;
postgresql

To remove the city column, use the following code:

ALTER TABLE customers DROP COLUMN city;
postgresql

This reduces the table to three columns:

id name address
1 Lee NULL
2 Johnson NULL
3 Smith NULL

Renaming a column with RENAME COLUMN

You can also rename an existing column. This can be a good alternative to deleting columns and then adding them again. The syntax for RENAME COLUMN is:

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;
postgresql

For example, you can change the name column to customer_name:

ALTER TABLE customers RENAME COLUMN name TO customer_name;
postgresql

Here’s what the table looks like now:

id customer_name address
1 Lee NULL
2 Johnson NULL
3 Smith NULL

Additional PostgreSQL actions for ALTER TABLE

Here are some other key actions you can use with ALTER TABLE:

Changing the data type of a column:

ALTER TABLE table_name ALTER COLUMN column_name TYPE data_type;
postgresql

Making sure that every entry in a column has a value:

ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;
postgresql

Establishing constraints such as UNIQUE or PRIMARY KEY by using ALTER TABLE with ADD CONSTRAINT:

ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_definition;
postgresql
Was this article helpful?
Go to Main Menu