PostgreSQL ALTER TABLE: Add, Rename & Drop Columns

PostgreSQL ALTER TABLE is the command you use to change a table after you have created it. With it you can add a column, rename a column, change a data type, or drop a column you no longer need. In this beginner guide you will learn each one with small examples you can paste into psql and run.

A quick reminder of two words. A table is a grid that stores data, like a spreadsheet. A column is one field in that grid, such as a name or an email. If you have not made a table yet, read our guide on how to create a table in PostgreSQL first.

What does ALTER TABLE do?

ALTER TABLE changes the structure of a table that already exists. It does not add or edit rows of data. Use UPDATE for that. Think of it as editing the column headings of your spreadsheet instead of the cells.

Here is the basic shape of the command. You write ALTER TABLE, then the table name, then the change you want.

ALTER TABLE table_name
  action;

The word action is a placeholder. In the next sections we will replace it with ADD COLUMN, RENAME COLUMN, and other real actions.

Set up a practice table

Let us create a small table called customers and put two rows in it. Run this in psql.

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT
);

INSERT INTO customers (name, email)
VALUES ('Ana', 'ana@example.com'),
       ('Ben', 'ben@example.com');

The first statement makes a table with three columns. SERIAL gives each row a number automatically, and PRIMARY KEY makes that number unique. NOT NULL means the name can never be empty. The second statement adds two customers.

Add a column with ADD COLUMN

Say you now want to store a phone number. Use ADD COLUMN.

ALTER TABLE customers
  ADD COLUMN phone TEXT;

Line 1 says which table to change. Line 2 says to add a new column called phone that holds text. Existing rows get NULL in the new column, which means no value yet.

You can also give the new column a default value, so old rows are filled in for you.

ALTER TABLE customers
  ADD COLUMN country TEXT DEFAULT 'Unknown';

DEFAULT sets the value used when none is given. Now Ana and Ben both have the country Unknown. Check with SELECT * FROM customers;

Rename a column with RENAME COLUMN

If a name is unclear, you can change it. Here we rename phone to phone_number.

ALTER TABLE customers
  RENAME COLUMN phone TO phone_number;

RENAME COLUMN takes the old name, then TO, then the new name. The data stays exactly the same. Only the label changes.

You can also rename the whole table.

ALTER TABLE customers
  RENAME TO clients;

ALTER TABLE clients
  RENAME TO customers;

The first statement renames the table to clients. The second one puts the name back so the rest of this guide still works.

Change a data type with ALTER COLUMN

A data type tells PostgreSQL what kind of value a column holds, such as text or a number. To change it, use ALTER COLUMN ... TYPE.

ALTER TABLE customers
  ALTER COLUMN phone_number TYPE VARCHAR(20);

VARCHAR(20) means text with at most 20 characters. If existing data cannot be converted, PostgreSQL shows an error and changes nothing. When converting text to a number you may need USING, for example ALTER COLUMN age TYPE INTEGER USING age::INTEGER.

Add or remove rules on a column

You can also add a rule (called a constraint) to an existing column. Here we make sure email is always filled in, and then undo it.

ALTER TABLE customers
  ALTER COLUMN email SET NOT NULL;

ALTER TABLE customers
  ALTER COLUMN email DROP NOT NULL;

SET NOT NULL forbids empty values. It fails if some rows already have NULL, so fix those first. DROP NOT NULL removes the rule again.

Delete a column with DROP COLUMN

When you no longer need a column, remove it.

ALTER TABLE customers
  DROP COLUMN country;

DROP COLUMN deletes the column and all the data in it. There is no undo button, so be careful. If you want to skip the error when the column is missing, write DROP COLUMN IF EXISTS country.

Quick reference table

Safety tips for beginners

• Practice on a test database, not on data you care about.
• Back up your data before using DROP COLUMN or changing a type.
• Run SELECT * FROM table_name; first to see what is inside.
• Use \d table_name in psql to view the current columns after each change.
• You can wrap changes in BEGIN; and ROLLBACK; to test them safely. Use COMMIT; to keep them.

In PostgreSQL, ALTER TABLE changes can be run inside a transaction, which is a group of steps that either all work or all get cancelled. This makes experiments much safer.