Introduction to Alter Column in PostgreSQL
Alter column in PostgreSQL used to change the structure of the table column, we can rename the table column, adding the new column to the table, delete the column from the table also have added constraints to the table. We can alter the column in PostgreSQL by using an alter table statement, using alter table statement we have to add a new column, renaming an existing column, changing the data type of column in PostgreSQL. We can modify the table of columns as per the requirement of the user.
How to Alter Column in PostgreSQL?
Below is the methods of alter column in PostgreSQL. To alter column in PostgreSQL user have superuser privileges or table owner privileges on same table to alter column in PostgreSQL.
Below is the example of we need to have table owner privileges on the table to the same.
- In below first example we have trying to add column using the db_test user but it will issue the error of “ERROR: must be the owner of relation stud1”. Because db_test user have not sufficient privileges to alter the column.
- In second example we have used Postgres user, Postgres user has sufficient privileges to add column in PostgreSQL.
Code #1
psql -U db_test -d testing
alter table stud1 ADD stud_id_new int;
psql -U postgres -d testing
alter table stud1 ADD stud_id_new int;
\d+ stud1;
Output:
We have using stud2 and student table to describe the example of alter column in PostgreSQL are as follows. Below is the table description of the student and stud2 table.
Code #2
\d+ student;
\d+ stud2;
Output:
Methods of Alter Column in PostgreSQL
Below is the method of the same:
1. Rename Column Name
In below example we have using alter table command to change the name of existing column. Below is syntax of alter table to change the name of column.
Syntax
Alter table existing_table_name RENAME COLUMN old_column_name TO new_column_name;
In above syntax alter table statement is defined as change the name of the column using alter table statement. Rename column is defined as rename existing column with new name.
Code:
ALTER TABLE student RENAME COLUMN stud_name TO stud_name_new;
\d+ student;
Output:
In above example we have change the table column name from stud_name to stud_name_new.
2. Add New Column
In below example we have added new column to table by using alter table statement. Below is syntax of alter table to add new column are as follows.
Syntax:
Alter table name_of_tableADD COLUMN column_name (Name of column which we have adding) data_type;
In above syntax alter table statement is defined as add the new column using alter table statement. Add column is defined as add new column to existing table.
Code:
ALTER TABLE student ADD COLUMN address varchar;
\d+ student;
Output:
3. Delete Column
We have delete column from table by using alter table statement in PostgreSQL. Below is syntax of alter table to delete column.
Syntax
Alter table name_of_tableDROP COLUMN column_name (Name of column which we have deleting);
In above syntax alter table statement is defined as drop new column using alter table statement. Drop column is the keyword used to deleting column.
Code:
ALTER TABLE student DROP COLUMN address;
\d+ student;
Output:
4. Add Constraints
In below example we have adding constraints. Below is the syntax of adding constraint in PostgreSQL.
Syntax
Alter table name_of_tableALTER COLUMN name_of_columnset name_of_constraint;
In above syntax alter table statement is defined as adding constraint to column. Alter column is used to add constraint to column.
Code:
Alter table stud2 ALTER COLUMN id set NOT NULL;
\d+ stud2;
Output:
5. Remove Constraint
In below example we have removing constraint. Below is the syntax of removing constraint.
Syntax:
Alter table name_of_tableDROP CONTRAINT name_of_constraint;
In above syntax alter table statement is defined as dropping constraint from column.Drop constraint is used to drop constraint from column.
Code:
ALTER TABLE student DROP CONSTRAINT "student_pkey";
\d+ student;
Output:
6. Data Type of Column
In below example we have changing the datatype of column. Below is the syntax to change the data type.
Syntax:
Alter table name_of_tableALTER COLUMN name_of_column data_type;
In above syntax alter table statement is defined as change the column data type. Alter column is used to change the column data type.
Code:
Alter table student ALTER COLUMN phone type varchar(10);
\d+ student;
Output:
Recommended Articles
This is a guide to Alter Column in PostgreSQL. Here we discuss an introduction to Alter Column in PostgreSQL, syntax, methods with examples to implement. You can also go through our other related articles to learn more –
