How to Add a Column in PostgreSQL?

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.

Examples

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:

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: