How to Drop a Column from a PostgreSQL Table

Introduction

When you’re using PostgreSQL to store data in tables, there may be situations where you need to drop a column from a table. Fortunately, it’s easy to tackle this task with the help of the PostgreSQL ALTER TABLE command. In this article, we’ll show you how to drop a column from a PostgreSQL table by walking you through some realistic examples.

Prerequisites

Before you can follow along with the examples we’ll be discussing in this article, you’ll need to have a couple of important prerequisites in place. First, you’ll need to make sure that PostgreSQL is installed. You can use the service postgresql status command to check if the status is active and then press CTRL + C to exit.

You’ll also need to have the psql interactive terminal installed. Type psql -V to verify that psql is installed and working properly.

Screenshot of a terminal window getting the PostgreSQL server status and psql version

Creating a PostgreSQL Table in ‘psql’

Let’s begin by creating a sample table that we can later use to drop a column. We’ll execute our SQL commands in psql. You can enter the psql command-line interface by typing “psql” and pressing Return.

Now, we’ll use the CREATE TABLE command to make a new PostgreSQL table. The syntax for the command is:

CREATE TABLE TABLE_NAME (column_name + data_type + constraints_if_any);

The command used to create our table will look like the following:

CREATE TABLE employee(id INT NOT NULL PRIMARY KEY, emp_fullname text, emp_address CHAR(50), emp_contact INT NOT NULL, salary REAL, join_date DATE);

Executing the code shown above will display the table’s columns and their datatypes:

TABLE "public.employee"
COLUMN | TYPE | Collation | NULLABLE | DEFAULT
--------------+---------------+-----------+----------+---------
id | INTEGER | | NOT NULL |
emp_fullname | text | | |
emp_address | CHARACTER(50) | | |
emp_contact | INTEGER | | NOT NULL |
salary | REAL | | |
join_date | DATE | | |

Dropping an Entire PostgreSQL Table

Now that we’ve created our table, let’s see if we can delete it.

WARNING! Once you delete a table and its records, it cannot be retrieved again.

Here’s the SQL statement used for dropping an entire PostgreSQL table:

DROP TABLE [IF EXISTS] TABLE_NAME;

IF EXISTS is an optional keyword used to avoid any errors if the table in question does not exists.

While it’s sometimes necessary to delete a whole time, there are other times when you might just want to delete a column from the table. In the next section, we’ll see if we can just drop a single column of the table’s data.

Using the ‘ALTER TABLE’ Command to Drop Columns from a PostgreSQL Table

The following SQL statement makes use of the ALTER TABLE DROP SQL command to drop a column from a table:

ALTER TABLE TABLE_NAME DROP COLUMN column_name;

Here’s an example of this command in use:

ALTER TABLE employee DROP COLUMN emp_contact;

In this example, we’re deleting the emp_contact column.

The command shown above should return a table response that looks something like this:

TABLE "public.employee"
COLUMN | TYPE | Collation | NULLABLE | DEFAULT
--------------+---------------+-----------+----------+---------
id | INTEGER | | NOT NULL |
emp_fullname | text | | |
emp_address | CHARACTER(50) | | |
salary | REAL | | |
join_date | DATE | | |

You can see that the emp_contact has been removed from the table.

NOTE: You can drop multiple columns from a table using the syntax

ALTER TABLE TABLE_NAME DROP COLUMN column_name1, column_name2;

In addition to deleting columns from a PostgreSQL table, you can also rename a column or even a table by using the ALTER TABLE command.

Using the ‘ALTER TABLE’ Command to Rename a PostgreSQL Table’s Column

Let’s look at an example that uses the ALTER TABLE SQL command to rename a column in a table. The basic syntax of this command is:

ALTER TABLE TABLE_NAME RENAME COLUMN old_name TO new_name;

We can rename a column in our employee table using the following command:

ALTER TABLE employee RENAME COLUMN emp_name TO emp_fullname;

The ALTER TABLE command can also be used to rename a table using the syntax shown below:

ALTER TABLE TABLE_NAME RENAME TO new_table_name;

Conclusion

When you’re managing data in PostgreSQL, it’s important to be familiar with the ALTER TABLE command. In this tutorial, we focused on the use of this command to delete a column from a PostgreSQL table; however, this command can also be used to rename both columns and tables. With the examples we provided as a guide, you’ll be able to manage the columns and tables in your own PostgreSQL database.

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.