PG Casts

Adding and Dropping Columns

Episode #28 hosted by Mary Lee

In this episode, we're going to explore how to add and drop columns in Postgres tables.

Postgres Version 11


In this example, we're going to be dealing with a dummy database that has a table called users, with the columns name and employee number.

We can select from this table and see that we already have data: 

select * from users;


To add a new column to our table, we use the "alter table" command, with the table we are changing. We then specify what action we are going to do to the table, which in our case is "add column". Finally, we specify our desired column name and type, and any constraints we may want on it.

Here, I'm adding a new column called email, with a unique constraint.

alter table users add column email varchar unique;

Now we can inspect our table and see our new column and constraint.

\d users


To remove a column from our table, we use the same "alter table" command as before, once again passing "users" as our table name. This time however, our action is going to be "drop column", and we're going to get rid of the email column that we just created.

alter table users drop column email;

The drop column command will automatically remove any constraints or indexes related to the column we are dropping. So if we're getting rid of our email column, we should see that the unique constraint for that column is also removed.

We can verify this by inspecting our table again.

\d users

Thanks for watching!


create table users (
  name varchar not null,
  employee_number varchar not null unique

insert into users
values ('Sam', 'EM1234'),
('Emily', 'EM2345');