How to Use ALTER TABLE in PostgreSQL

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

After you’ve created a table in PostgreSQL, you may find you need to make changes to its structure. You might want to add a new column, delete a column or even rename an existing column. All of these changes are possible with the help of the ALTER TABLE statement. In this article, we’ll provide a series of examples to show you how to use ALTER TABLE in PostgreSQL to modify an existing table.

Prerequisites

Before diving into the examples in this tutorial, be sure the following prerequisites are in place:

  • PostgreSQL must be installed and running in the background.
  • You should have a basic understanding of PostgreSQL in order to follow along with the instructions and examples in this article.

PostgreSQL DROP COLUMN in a Table

We’ll begin by looking at the basic form of the ALTER TABLE syntax:

1
2
ALTER TABLE TABLE_NAME
  action;

Let’s discuss this syntax in a bit more detail:

First, we indicate the name of the table right after the ALTER TABLE statement. This specifies the table we wish to modify in some way.

We then specify which action should be taken. The following actions can be performed:

  • ADD COLUMN: allows us to add one or more columns to a target table
  • DROP COLUMN: allows us to remove or drop one or more columns from a target table
  • RENAME COLUMN: allows us to change the name of columns in the table
  • SET DATA TYPE: allows us to change the data type of a specific column in the table

PostgreSQL ALTER TABLE Actions with Example

In this section, we’ll show some examples of how to use PostgreSQL ALTER TABLE statements with the various actions described above.

We’ll need a sample table to use in these examples. Our sample table ‘entity’ can be created with the following statement:

1
2
3
4
5
6
7
CREATE TABLE entity(
    entity_id INT PRIMARY KEY NOT NULL,
    entity_name VARCHAR NOT NULL,
    entity_lastname VARCHAR NOT NULL,
    entity_age INT,
    entity_gender VARCHAR
);

We now have a table with the structure shown below:

1
2
3
4
5
6
7
8
9
10
                        Table "public.entity"
     Column      |       Type        | Collation | Nullable | Default
-----------------+-------------------+-----------+----------+---------
 entity_id       | integer           |           | not null |
 entity_name     | character varying |           | not null |
 entity_lastname | character varying |           | not null |
 entity_age      | integer           |           |          |
 entity_gender   | character varying |           |          |
Indexes:
    "entity_pkey" PRIMARY KEY, btree (entity_id)

ALTER TABLE ADD COLUMN

Our first example will illustrate how to alter our table by adding a new column called email. The ALTER TABLE statement uses the following basic syntax when a column is being added:

1
ALTER TABLE the_table_name <ADD COLUMN> new_column_name <DATA TYPE>;

For our example, we’ll use the statement shown below:

1
ALTER TABLE entity ADD COLUMN email VARCHAR;

We can verify that this statement was executed successfully using the ‘d’ command:

1
2
3
4
5
6
7
8
9
10
11
                        Table "public.entity"
     Column      |       Type        | Collation | Nullable | Default
-----------------+-------------------+-----------+----------+---------
 entity_id       | integer           |           | not null |
 entity_name     | character varying |           | not null |
 entity_lastname | character varying |           | not null |
 entity_age      | integer           |           |          |
 entity_gender   | character varying |           |          |
 email           | character varying |           |          |
Indexes:
    "entity_pkey" PRIMARY KEY, btree (entity_id)

We can see that the new field named email was added with the specified data type.

ALTER TABLE DROP COLUMN

In our next example, we will remove the email column from our table.

The following statement will remove the column:

1
ALTER TABLE entity DROP COLUMN email;

Let’s use \d again to describe the structure of the table again and see if our ALTER TABLE statement was successful:

1
2
3
4
5
6
7
8
9
10
                        Table "public.entity"
     Column      |       Type        | Collation | Nullable | Default
-----------------+-------------------+-----------+----------+---------
 entity_id       | integer           |           | not null |
 entity_name     | character varying |           | not null |
 entity_lastname | character varying |           | not null |
 entity_age      | integer           |           |          |
 entity_gender   | character varying |           |          |
Indexes:
    "entity_pkey" PRIMARY KEY, btree (entity_id)

ALTER TABLE RENAME COLUMN

To rename a table using the ALTER TABLE statement, we use the following basic syntax:

1
ALTER TABLE the_table_name <RENAME COLUMN> old_column_name TO new_column_name;

Using this syntax, we can rename a column in our entity table with the ALTER TABLE statement shown below:

1
ALTER TABLE entity RENAME COLUMN entity_gender TO entity_sex;

Our new table should look like something like this:

1
2
3
4
5
6
7
8
9
10
                        Table "public.entity"
     Column      |       Type        | Collation | Nullable | Default
-----------------+-------------------+-----------+----------+---------
 entity_id       | integer           |           | not null |
 entity_name     | character varying |           | not null |
 entity_lastname | character varying |           | not null |
 entity_age      | integer           |           |          |
 entity_sex      | character varying |           |          |
Indexes:
    "entity_pkey" PRIMARY KEY, btree (entity_id)

ALTER TABLE SET DATA

To change the data type of an existing column, we use the following basic syntax of the ALTER TABLE statement:

1
2
ALTER TABLE the_table_name
<ALTER COLUMN> the_column_name [SET DATA] TYPE new_data_type;

In our example, we’ll change the data type for entity_age to VARCHAR. Our statement will look like this:

1
2
ALTER TABLE entity
ALTER COLUMN entity_age TYPE VARCHAR;

Let’s describe the table using \d one more time. It should now look something like this:

1
2
3
4
5
6
7
8
9
10
                        Table "public.entity"
     Column      |       Type        | Collation | Nullable | Default
-----------------+-------------------+-----------+----------+---------
 entity_id       | integer           |           | not null |
 entity_name     | character varying |           | not null |
 entity_lastname | character varying |           | not null |
 entity_age      | character varying |           |          |
 entity_sex      | character varying |           |          |
Indexes:
    "entity_pkey" PRIMARY KEY, btree (entity_id)

Notice that the data type for the column entity_age changed from integer to character varying.

Conclusion

PostgreSQL tables aren’t set in stone once they’re created. They can be modified in a number of ways: Columns can be added, removed and renamed, and the data type of a column can be changed. In this article, we demonstrated how to perform all these actions using ALTER TABLE in PostgreSQL. If you’ve followed along with our examples, you’ll be prepared to make changes to tables in your own PostgreSQL environment.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.