PostgreSQL ALTER Table

The ALTER command is used to add, remove, or modify columns from the existing structure of the created table.The ALTER TABLE command is used to add and drop various constraints to an existing table.

PostgreSQL ALTER Syntax:

The syntax is as follows:

ALTER TABLE table-name
action;

These are some of the following actions:

1. Used to add a CHECK constraint to a column.
2. Used to rename a table.
3. Used to set a default value for the column.
4. Used to add a column, drop a column, rename a column, or change the data type of a column.

Syntax:

\\ALTER TABLE to add a new column in an existing table
ALTER TABLE table_name ADD column_name datatype;
---------------------------------------------------------
\\ALTER TABLE to DROP COLUMN in an existing table
ALTER TABLE table_name DROP COLUMN column_name;
---------------------------------------------------------
\\ALTER TABLE to change the DATA TYPE of a column
ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;
---------------------------------------------------------
\\ALTER TABLE to add a NOT NULL constraint to a column
ALTER TABLE table_name MODIFY column_name datatype NOT NULL;
----------------------------------------------------------
\\ALTER TABLE to ADD UNIQUE CONSTRAINT to a table 
ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);
------------------------------------------------------------
\\ALTER TABLE to ADD CHECK CONSTRAINT to a table
ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);
------------------------------------------------------------
\\ALTER TABLE to ADD PRIMARY KEY constraint 
ALTER TABLE table_name
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);
--------------------------------------------------------------
\\ALTER TABLE to DROP CONSTRAINT from a table
ALTER TABLE table_name
DROP CONSTRAINT MyUniqueConstraint;

ALTER Example :

Consider the STUDENT table is having the following records

Following example will add a new column in an existing table −

ALTER TABLE STUDENT ADD GENDER char(1);

STUDENT table is changed and the following would be the output from SELECT statement −

Following example will add a new column in an existing table −
STUDENT table is changed and the following would be the output from SELECT statement.

ALTER TABLE STUDENT DROP GENDER;

---

Visit :

---

Discussion