# How to add or remove columns in PostgreSQL with ALTER TABLE

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](https://www.ionos.com/digitalguide/server/know-how/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](https://www.ionos.com/digitalguide/hosting/technical-matters/database-management-systems-dbms/). To use this command, you need to combine it with a specific action.

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

```postgresql
ALTER TABLE table_name action;
```

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](https://www.ionos.com/digitalguide/server/configuration/postgresql-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:

<table>
  <thead>
    <tr>
      <th>id</th>
      <th>name</th>
      <th>city</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>1</td>
      <td>Lee</td>
      <td>New York</td>
    </tr>
    <tr>
      <td>2</td>
      <td>Johnson</td>
      <td>Los Angeles</td>
    </tr>
    <tr>
      <td>3</td>
      <td>Vargas</td>
      <td>Chicago</td>
    </tr>
  </tbody>
</table>

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:

```postgresql
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:

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

Here’s what the table looks like now:

<table>
  <thead>
    <tr>
      <th>id</th>
      <th>name</th>
      <th>city</th>
      <th>address</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>1</td>
      <td>Lee</td>
      <td>New York</td>
      <td>NULL</td>
    </tr>
    <tr>
      <td>2</td>
      <td>Johnson</td>
      <td>Los Angeles</td>
      <td>NULL</td>
    </tr>
    <tr>
      <td>3</td>
      <td>Vargas</td>
      <td>Chicago</td>
      <td>NULL</td>
    </tr>
  </tbody>
</table>

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

```postgresql
ALTER TABLE table_name DROP COLUMN column_name;
```

To remove the `city` column, use the following code:

```postgresql
ALTER TABLE customers DROP COLUMN city;
```

This reduces the table to three columns:

<table>
  <thead>
    <tr>
      <th>id</th>
      <th>name</th>
      <th>address</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>1</td>
      <td>Lee</td>
      <td>NULL</td>
    </tr>
    <tr>
      <td>2</td>
      <td>Johnson</td>
      <td>NULL</td>
    </tr>
    <tr>
      <td>3</td>
      <td>Vargas</td>
      <td>NULL</td>
    </tr>
  </tbody>
</table>

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

```postgresql
ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;
```

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

```postgresql
ALTER TABLE customers RENAME COLUMN name TO customer_name;
```

Here’s what the table looks like now:

<table>
  <thead>
    <tr>
      <th>id</th>
      <th>customer\_name</th>
      <th>address</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>1</td>
      <td>Lee</td>
      <td>NULL</td>
    </tr>
    <tr>
      <td>2</td>
      <td>Johnson</td>
      <td>NULL</td>
    </tr>
    <tr>
      <td>3</td>
      <td>Vargas</td>
      <td>NULL</td>
    </tr>
  </tbody>
</table>

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

```postgresql
ALTER TABLE table_name ALTER COLUMN column_name TYPE data_type;
```

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

```postgresql
ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;
```

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

```postgresql
ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_definition;
```


This is a markdown version of: [https://www.ionos.com/digitalguide/server/configuration/postgresql-alter-table/](https://www.ionos.com/digitalguide/server/configuration/postgresql-alter-table/) for AI/LLM consumption.