The ALTER TABLE statement allows you to modify the structure of an existing table without deleting or recreating it.
1. Add a Column
Syntax
ALTER TABLE table_name
ADD COLUMN column_name data_type;
Example
ALTER TABLE customers
ADD COLUMN date_of_birth DATE;
2. Drop a Column
Syntax
ALTER TABLE table_name
DROP COLUMN column_name;
Example
ALTER TABLE customers
DROP COLUMN date_of_birth;
3. Rename a Column
Syntax
ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;
Example
ALTER TABLE products
RENAME COLUMN stock TO stock_quantity;
4. Rename a Table
Syntax
ALTER TABLE old_table_name
RENAME TO new_table_name;
Example
ALTER TABLE customers
RENAME TO clients;
5. Change a Column's Data Type
Syntax
ALTER TABLE table_name
ALTER COLUMN column_name TYPE new_data_type;
Example
ALTER TABLE products
ALTER COLUMN product_name TYPE VARCHAR(150);
6. Set a Default Value
Syntax
ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT value;
Example
ALTER TABLE products
ALTER COLUMN stock_quantity SET DEFAULT 0;
7. Drop a Default Value
Syntax
ALTER TABLE table_name
ALTER COLUMN column_name DROP DEFAULT;
Example
ALTER TABLE products
ALTER COLUMN stock_quantity DROP DEFAULT;
8. Add a NOT NULL Constraint
Syntax
ALTER TABLE table_name
ALTER COLUMN column_name SET NOT NULL;
Example
ALTER TABLE customers
ALTER COLUMN email SET NOT NULL;
9. Drop a NOT NULL Constraint
Syntax
ALTER TABLE table_name
ALTER COLUMN column_name DROP NOT NULL;
Example
ALTER TABLE customers
ALTER COLUMN email DROP NOT NULL;
10. Add a CHECK Constraint
Syntax
ALTER TABLE table_name
ADD CONSTRAINT constraint_name
CHECK (condition);
Example
ALTER TABLE products
ADD CONSTRAINT chk_price
CHECK (unit_price >= 0);
11. Drop a Constraint
Syntax
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
Example
ALTER TABLE products
DROP CONSTRAINT chk_price;
12. Add a UNIQUE Constraint
ALTER TABLE customers
ADD CONSTRAINT uq_email
UNIQUE (email);
13. Add a PRIMARY KEY
ALTER TABLE customers
ADD CONSTRAINT pk_customers
PRIMARY KEY (customer_id);
14. Add a FOREIGN KEY
ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id);
15. Add Multiple Columns
ALTER TABLE customers
ADD COLUMN county VARCHAR(50),
ADD COLUMN postal_code VARCHAR(20);
16. Perform Multiple Changes
ALTER TABLE products
ALTER COLUMN product_name TYPE VARCHAR(150),
ALTER COLUMN stock_quantity SET DEFAULT 0,
ALTER COLUMN unit_price SET NOT NULL;
17. Rename a Constraint
ALTER TABLE customers
RENAME CONSTRAINT customers_pkey TO pk_customers;
18. Move a Table to Another Schema
ALTER TABLE customers
SET SCHEMA sales;
19. Change Table Owner
ALTER TABLE customers
OWNER TO postgres;
Quick Reference
| Task | Command |
|---|---|
| Add Column | ADD COLUMN |
| Drop Column | DROP COLUMN |
| Rename Column | RENAME COLUMN |
| Rename Table | RENAME TO |
| Change Data Type | ALTER COLUMN ... TYPE |
| Set Default | ALTER COLUMN ... SET DEFAULT |
| Drop Default | ALTER COLUMN ... DROP DEFAULT |
| Set NOT NULL | ALTER COLUMN ... SET NOT NULL |
| Drop NOT NULL | ALTER COLUMN ... DROP NOT NULL |
| Add CHECK | ADD CONSTRAINT ... CHECK |
| Drop Constraint | DROP CONSTRAINT |
| Add UNIQUE | ADD CONSTRAINT ... UNIQUE |
| Add PRIMARY KEY | ADD CONSTRAINT ... PRIMARY KEY |
| Add FOREIGN KEY | ADD CONSTRAINT ... FOREIGN KEY |
| Rename Constraint | RENAME CONSTRAINT |
| Move Schema | SET SCHEMA |
| Change Owner | OWNER TO |
Top comments (0)