How to add or remove columns in Post­gr­eSQL with ALTER TABLE

The ALTER TABLE command in Post­gr­eSQL lets you add or modify columns in database tables.

What is Post­gr­eSQL’s ALTER TABLE?

The ALTER TABLE command in Post­gr­eSQL 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 con­straints on a table in the database man­age­ment system. To use this command, you need to combine it with a specific action.

What is the syntax for ALTER TABLE?

To get a better un­der­stand­ing of how to use ALTER TABLE, let’s first take a look at its syntax:

ALTER TABLE table_name action;

After the command, you need to specify the name of the table you want to modify and choose the cor­res­pond­ing action for the changes you want to make.

Post­gr­eSQL ALTER TABLE examples

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

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

Adding a column with Post­gr­eSQL ADD COLUMN

To add a new column, use ALTER TABLE in com­bin­a­tion with Post­gr­eSQL’s ADD COLUMN action. This action requires two para­met­ers: 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;

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

ALTER TABLE customers ADD COLUMN address VARCHAR(255);

Here’s what the table looks like now:

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;

To remove the city column, use the following code:

ALTER TABLE customers DROP COLUMN city;

This reduces the table to three columns:

Renaming a column with RENAME COLUMN

You can also rename an existing column. This can be a good al­tern­at­ive 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;

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

ALTER TABLE customers RENAME COLUMN name TO customer_name;

Here’s what the table looks like now:

Ad­di­tion­al Post­gr­eSQL 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;

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

ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;

Es­tab­lish­ing con­straints such as UNIQUE or PRIMARY KEY by using ALTER TABLE with ADD CONSTRAINT:

ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_definition;