PostgreSQL ALTER TABLE

Business needs shift. A shop that once stored only a customer name now needs a phone number. A column that held short codes now needs longer text. ALTER TABLE changes a table without deleting it, so existing rows survive.

  Before:  customers(id, name)
              |
           ALTER TABLE
              |
  After:   customers(id, name, phone)

Think of a table as a printed form. ALTER TABLE lets you add a new box, rename a box, or remove a box on the form while keeping every filled-in copy.

Adding, Removing, and Renaming

Four small commands cover the most common structure changes. Each one touches a single column or the table name.

Adding a Column

ALTER TABLE customers
ADD COLUMN phone VARCHAR(20);

Existing rows receive NULL in the new column. Supply a default value to fill them with something useful.

ALTER TABLE customers
ADD COLUMN status TEXT DEFAULT 'active';

Dropping a Column

ALTER TABLE customers
DROP COLUMN phone;

PostgreSQL deletes the column and all its data. Take a backup before you run this on important tables. Add IF EXISTS to avoid an error when the column is missing.

Renaming a Column

ALTER TABLE customers
RENAME COLUMN name TO full_name;

Queries and views that use the old name will fail after the change. Search your code for the old name before renaming.

Renaming a Table

ALTER TABLE customers
RENAME TO clients;

Changing Column Rules

Existing columns can switch type, default value, or null behavior without losing their rows.

Changing a Data Type

Use ALTER COLUMN with TYPE. PostgreSQL converts the stored values when it can.

ALTER TABLE products
ALTER COLUMN price TYPE NUMERIC(10,2);

Some conversions need instructions. Changing text to an integer needs a USING clause that explains how to convert each value.

ALTER TABLE products
ALTER COLUMN stock TYPE INTEGER
USING stock::INTEGER;

  Text '25'  --USING stock::INTEGER-->  Number 25

Setting and Removing Defaults

ALTER TABLE products
ALTER COLUMN stock SET DEFAULT 0;

ALTER TABLE products
ALTER COLUMN stock DROP DEFAULT;

NOT NULL Rules

Require a value in a column with SET NOT NULL. Every existing row must already have a value, or the command fails.

ALTER TABLE products
ALTER COLUMN name SET NOT NULL;

ALTER TABLE products
ALTER COLUMN name DROP NOT NULL;

Fill missing values first with an UPDATE, and run SET NOT NULL afterward.

Constraints and Combined Changes

Rules between tables and bundled edits give you control over data quality and over how many commands you run.

Adding and Dropping Constraints

ALTER TABLE products
ADD CONSTRAINT price_positive CHECK (price > 0);

ALTER TABLE products
ADD CONSTRAINT sku_unique UNIQUE (sku);

ALTER TABLE products
DROP CONSTRAINT price_positive;

Name every constraint. A clear name such as price_positive makes error messages easy to read and makes later removal simple.

Adding a Foreign Key

ALTER TABLE orders
ADD CONSTRAINT fk_orders_client
FOREIGN KEY (client_id) REFERENCES clients(id);

  orders.client_id  ------>  clients.id
  (child)                    (parent)

Several Changes in One Command

Combine actions with commas. PostgreSQL applies them together as one operation.

ALTER TABLE products
  ADD COLUMN brand TEXT,
  ALTER COLUMN price SET NOT NULL,
  RENAME COLUMN name TO product_name;

Renames cannot share a command with other actions in every version. Run RENAME as a separate statement if you see an error.

Safety Checklist

• Back up the table before destructive changes.
• Test the command on a copy of the data.
• Wrap the change in a transaction so you can roll back.
• Run large changes during quiet hours, because some changes lock the table.

BEGIN;
ALTER TABLE products ADD COLUMN weight NUMERIC;
-- check the result
ROLLBACK;  -- or COMMIT;