PostgreSQL ALTER TABLE command is used to modify the structure of PostgreSQL table. ALTER TABLE ADD COLUMN will only add the new column at the end, as the last one. PostgreSQL ALTER TABLE statement can do following actions on a table:
- Add column to Table
- Rename Table name
- Drop column from table
- Change the data type and its length
- Add or drop constraints
- Rename column name
- Change Storage Engine Type
……….
PostgreSQL ALTER TABLE Syntax:
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ] action [, ... ] ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ] RENAME [ COLUMN ] column_name TO new_column_name ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ] RENAME CONSTRAINT constraint_name TO new_constraint_name ALTER TABLE [ IF EXISTS ] name RENAME TO new_name
Where action can be add or drop column, constraint, ..etc.
PostgreSQL ALTER TABLE Examples:
Create a table.
CREATE TABLE student(SNO int,sname varchar(90), dob date,class int,gender varchar(1));
1. How to add column to PostgreSQL Table
ALTER TABLE student ADD email varchar(90);
To verify run the command \d+ table_name.
r2schools=# \d+ student Table "public.student" Column | Type | Collation | Nullable | Default | Storage | --------+-----------------------+-----------+----------+---------+----------+- sno | integer | | | | plain | sname | character varying(90) | | | | extended | dob | date | | | | plain | class | integer | | | | plain | gender | character varying(1) | | | | extended | email | character varying(90) | | | | extended | Access method: heap
2. How to delete or remove column from PostgreSQL Table
ALTER TABLE student drop column email;
3. How to modify PostgreSQL Table Column
Change the sname column length to 100
ALTER TABLE student ALTER COLUMN sname TYPE varchar(100);
4. How to add constraint on PostgreSQL Table
ALTER TABLE student ADD CONSTRAINT TYPE PRIMARY KEY(SNO);
5. How to rename table in PostgreSQL
ALTER TABLE student rename to students;
Visual presentation of above commands in order.
