How to Add a Column in PostgreSQL?
Overview
The ADD column in Postgresql is used along with the ALTER clause for inserting one or more columns in a table. The ADD COLUMN command in Postgresql also allows us to insert the columns with constraints such as UNIQUE, NOT NULL, etc.
Transform Your Career
Choose from our industry-leading programs designed for career success
Modern Software and AI Engineering Program
Master full-stack development with AI integration
+1000 moreModern Data Science and ML with specialisation in AI
Advanced data science techniques with AI specialization
+1000 moreAdvanced AIML with Specialisation in Agentic AI
Deep dive into AIML with focus on Agentic systems
+1000 moreDevOps, Cloud & AI Platform Engineering
Build and manage AI-powered cloud infrastructure
+1000 moreAI Engineering Advanced Certification by IIT-Roorkee
Premier AI engineering certification from IIT-Roorkee
How to Insert a Column to a Table in Postgres?
ALTER TABLE with ADD COLUMN command inserts a column in Postgresql.
Syntax:
The syntax for inserting a column in a table in PostgreSQL is given below
The following parameters are used in the command given above:
- Alter table:
Alter table is a clause used to modify the table. It allows us to modify a column, rename a column rename a table, add a column, and drop a column. - tableName:
It is written after the Alter table clause and it is used to specify the name of the table in which we want to insert a column. - ADD COLUMN:
It is a clause that specifies that we want to insert a column in the table. - newColumnName:
It is written after the ADD COLUMN clause and here we write the name of the column which we want to insert. - data_type:
It specifies the data type of the column that is to be added to the table
When a new column is inserted in the table, that column will be added at the end of the table in Postgresql.
How to Add Multiple Columns to a Particular Table in Postgres?
ADD COLUMN in Postgresql along with the ALTER TABLE clause can also be used for inserting multiple columns in a table.
Syntax:
The syntax for inserting multiple columns in a single table in Postgresql is given below:
We can add multiple columns by using the ALTER TABLE clause, for every column only we need to write the ADD COLUMN clause along with the column name, data type, and its constraint.
How to Add a Column with Constraint?
Postgresql ADD COLUMN clause also allows us to add a column in a table with constraints like UNIQUE, DEFAULT, NOT NULL, etc.
Syntax:
The syntax of inserting a column with a constraint is given below.
The following parameters are used in the command given above:
- ALTER TABLE command is used to modify the table.
- tableName specifies the name of the table in which we want to add a column with a constraint.
- ADD COLUMN clause insets one or more columns in a table.
- columnName specifies the name of the column which is to be inserted in a table.
- data_type specifies the data type of column to be inserted like varchar, integer, etc.
- constraints is used to specify the rules for a column to be inserted such as NOT NULL, UNIQUE, etc.
Scaler Placement Report and Statistics
Scaler learners achieved 2.5x salary growth with average post-Scaler CTC reaching ₹23L.
Google
Amazon
Microsoft
Flipkart
Adobe1200+ moreExamples
Let us take an example of the Student table, so firstly we will create a table Student with two columns Student_id and Student_name by the following command.
Now we will run the SELECT query to check the schema of the table:
Query:
Output:
Example - 1: Inserting a Column to a Table
Here we are adding a column named student_fatherName having the varchar type in a Student table.
Query:
Now we will run the SELECT query to verify that column is added in a table:
Query:
Output:
Turn Learning into Career Growth
Example - 2: Adding Multiple Columns to a Particular Table
Here we are adding two columns named Address and City in the Student table.
Query:
Now we will run the SELECT query to verify that columns are added in a table:
Query:
Output:
Example - 3: Adding a Column with Constraint
If we want to add an Email column with no null constraint. Then we can write the below command.
Query:
Output:
Now we will go to the properties of the table to verify whether a column is added with the constraint or not:
Conclusion
- ADD COLUMN in Postgresql is used along with the ALTER TABLE clause for inserting one or more columns in a table.
- Postgresql ADD COLUMN clause also allows us to add a column in a table with constraints like UNIQUE, DEFAULT, NOT NULL, etc.
- We can add multiple columns by using the single ALTER TABLE clause, and multiple ADD COLUMN clause.
