PostgreSQL
Altering Tables
Real databases evolve. Requirements change, new features get added, column names turn out to be confusing, and data types need upgrading. You will almost never get a table design perfect on the first attempt — and that is fine. PostgreSQL gives you the ALTER TABLE command to modify any table structure safely without losing data. This lesson covers every common alteration you will need in real projects, all practiced on the Dataplexa Store tables.
The ALTER TABLE Command
ALTER TABLE is the Swiss Army knife of table management. One command handles adding columns, removing columns, renaming things, changing data types, and managing constraints. The syntax always follows the same pattern — you name the table, then describe what you want to change.
-- General syntax
ALTER TABLE table_name action;
-- Multiple actions in one statement (more efficient — one table lock)
ALTER TABLE table_name
action_one,
action_two,
action_three;Adding a Column
Adding a column is the most common table alteration. New business requirements often mean new information needs to be tracked. The new column is added to the end of the table and all existing rows get NULL for that column unless you specify a DEFAULT.
-- Add a single column to the customers table
ALTER TABLE customers ADD COLUMN loyalty_points INTEGER DEFAULT 0;
-- Add a column with NOT NULL — must have a DEFAULT for existing rows
ALTER TABLE customers ADD COLUMN tier VARCHAR(20) NOT NULL DEFAULT 'standard';
-- Add multiple columns at once
ALTER TABLE products
ADD COLUMN weight_kg NUMERIC(8,2),
ADD COLUMN manufacturer VARCHAR(100),
ADD COLUMN warranty_yrs INTEGER DEFAULT 1;
-- Verify the new columns exist
\d customersALTER TABLE
ALTER TABLE
Table "public.customers"
Column | Type | Nullable | Default
----------------+------------------+----------+---------------
id | integer | not null | nextval(...)
first_name | character varying| not null |
email | character varying| not null |
loyalty_points | integer | | 0
tier | character varying| not null | 'standard'
Notice that when you add a NOT NULL column to a table that already has data, you must also provide a DEFAULT. Without a default, PostgreSQL has no value to put into that column for the existing rows — and since the column is NOT NULL, it cannot leave them as NULL. The DEFAULT fills those existing rows immediately.
Dropping a Column
Removing a column permanently deletes it and all the data it contained. This cannot be undone. If other objects — views, indexes, or constraints — depend on the column, use CASCADE to remove them along with it.
-- Drop a single column
ALTER TABLE customers DROP COLUMN loyalty_points;
-- Drop a column and anything that depends on it
ALTER TABLE customers DROP COLUMN tier CASCADE;
-- Drop multiple columns at once
ALTER TABLE products
DROP COLUMN weight_kg,
DROP COLUMN manufacturer;ALTER TABLE
ALTER TABLE
Renaming a Column
Column names sometimes need to change as a project matures — a name that made sense early on can become ambiguous or inconsistent with the rest of the schema. Renaming a column updates all internal references in PostgreSQL automatically, but any application code or queries that use the old name will break and need to be updated.
-- Rename a column
ALTER TABLE customers RENAME COLUMN joined_date TO registration_date;
-- Rename another column in products
ALTER TABLE products RENAME COLUMN stock_qty TO quantity_available;
-- Rename in employees
ALTER TABLE employees RENAME COLUMN hire_date TO start_date;ALTER TABLE
ALTER TABLE
Renaming a Table
You can rename an entire table just as easily. Like renaming a column, all internal PostgreSQL references update automatically — foreign keys, indexes, and sequences all track the new name. Application code referencing the old table name will need updating.
-- Rename a table
ALTER TABLE order_items RENAME TO line_items;
-- Rename it back
ALTER TABLE line_items RENAME TO order_items;ALTER TABLE
Changing a Column's Data Type
Sometimes a column's data type needs to change — a phone number stored as INTEGER needs to become TEXT to support formatting like (555) 123-4567, or a price column stored as REAL needs to become NUMERIC for exact precision. PostgreSQL performs the type change and automatically converts all existing values when a safe conversion path exists.
-- Change a column from INTEGER to BIGINT (safe — larger range)
ALTER TABLE orders ALTER COLUMN id TYPE BIGINT;
-- Change a column from VARCHAR(50) to VARCHAR(100) (safe — larger size)
ALTER TABLE customers ALTER COLUMN first_name TYPE VARCHAR(100);
-- Change with explicit type casting using USING
-- When PostgreSQL cannot convert automatically, you tell it how
ALTER TABLE employees
ALTER COLUMN salary TYPE INTEGER
USING salary::INTEGER; -- cast NUMERIC to INTEGER (truncates decimals)
-- Verify the change
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'employees' AND column_name = 'salary';ALTER TABLE
ALTER TABLE
column_name | data_type
-------------+-----------
salary | integer
The USING clause is required whenever PostgreSQL cannot automatically figure out how to convert existing values. salary::INTEGER explicitly casts the salary to an integer — any decimal portion is truncated. Always think carefully about what happens to existing data before changing a type.
Setting and Dropping Column Defaults
-- Add a default to an existing column
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'pending';
-- Change an existing default
ALTER TABLE products ALTER COLUMN stock_qty SET DEFAULT 10;
-- Remove a default entirely
ALTER TABLE products ALTER COLUMN stock_qty DROP DEFAULT;
-- Check current defaults
SELECT column_name, column_default
FROM information_schema.columns
WHERE table_name = 'orders';ALTER TABLE
ALTER TABLE
column_name | column_default
--------------+-----------------
id | nextval(...)
status | 'pending'
order_date | CURRENT_DATE
total_amount |
Adding and Dropping NOT NULL
-- Make an existing column required
-- Ensure no existing NULLs first, then apply
UPDATE customers SET state = 'N/A' WHERE state IS NULL;
ALTER TABLE customers ALTER COLUMN state SET NOT NULL;
-- Make a column optional again
ALTER TABLE customers ALTER COLUMN state DROP NOT NULL;ALTER TABLE
ALTER TABLE
Adding and Dropping Constraints
-- Add a UNIQUE constraint to an existing column
ALTER TABLE customers ADD CONSTRAINT customers_email_unique UNIQUE (email);
-- Add a CHECK constraint
ALTER TABLE products ADD CONSTRAINT products_price_positive CHECK (price > 0);
-- Add a FOREIGN KEY
ALTER TABLE orders ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT;
-- Drop a named constraint
ALTER TABLE products DROP CONSTRAINT products_price_positive;
-- View all constraints on a table
SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'orders';ALTER TABLE
ALTER TABLE
ALTER TABLE
constraint_name | constraint_type
--------------------------------+-----------------
orders_pkey | PRIMARY KEY
fk_orders_customer | FOREIGN KEY
customers_email_unique | UNIQUE
Changing Table Ownership
In multi-user PostgreSQL environments, tables are owned by the user who created them. You can transfer ownership to another user using ALTER TABLE. Only the current owner or a superuser can do this.
-- Transfer a table to a different user
ALTER TABLE customers OWNER TO app_user;
-- Transfer back to postgres
ALTER TABLE customers OWNER TO postgres;ALTER TABLE
ALTER TABLE Quick Reference
| Action | Syntax |
|---|---|
| Add column | ALTER TABLE t ADD COLUMN col type; |
| Drop column | ALTER TABLE t DROP COLUMN col; |
| Rename column | ALTER TABLE t RENAME COLUMN old TO new; |
| Rename table | ALTER TABLE old RENAME TO new; |
| Change data type | ALTER TABLE t ALTER COLUMN col TYPE newtype; |
| Set default | ALTER TABLE t ALTER COLUMN col SET DEFAULT val; |
| Drop default | ALTER TABLE t ALTER COLUMN col DROP DEFAULT; |
| Set NOT NULL | ALTER TABLE t ALTER COLUMN col SET NOT NULL; |
| Drop NOT NULL | ALTER TABLE t ALTER COLUMN col DROP NOT NULL; |
| Add constraint | ALTER TABLE t ADD CONSTRAINT name type; |
| Drop constraint | ALTER TABLE t DROP CONSTRAINT name; |
🧪 Practice Questions
Answer based on what you learned in this lesson.
1. What ALTER TABLE clause adds a new column to an existing table?
2. What keyword do you add to a TYPE change when PostgreSQL cannot automatically convert existing values?
3. What keyword forces a column drop to also remove dependent views and constraints?
4. What ALTER TABLE clause renames an existing column?
5. When adding a NOT NULL column to a table that already has rows, what must you always provide to avoid an error?
🎯 Quiz — Test Your Understanding
Q1. Why is it better to combine multiple ALTER TABLE actions into a single statement?
Q2. What happens to existing rows when you add a new column with a DEFAULT value?
Q3. Which statement correctly changes the salary column in employees from INTEGER to NUMERIC(10,2)?
Q4. What is the key risk when renaming a table in PostgreSQL?
Q5. You want to make an existing nullable column required. The column currently has some NULL values. What is the correct approach?
Next up: Removing tables entirely and clearing all their data with DROP and TRUNCATE.