For inserting a new field in an existing table, use the ALTER statement. The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. To add a new column to an existing table, use the ADD COLUMN
statement.
Syntax
ALTER TABLE {TABLE_NAME}
ADD {COLUMN_NAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES;
Specify the name of the table that you want to add a new column to after the ALTER TABLE
keyword and then specify the name of the new column with its data type and constraint after the ADD COLUMN_NAME keywords.
Refer the below example for better understanding.
ALTER TABLE actors
ADD actors_age INT NOT NULL
CONSTRAINT CONSTRAINT_NAME DEFAULT 0
Syntax for adding multiple columns
ALTER TABLE {TABLE_NAME}
ADD {COLUMN_NAME_1} {TYPE} {NULL|NOT NULL} CONSTRAINT {CONSTRAINT_NAME},
ADD {COLUMN_NAME_2} {TYPE} {NULL|NOT NULL} CONSTRAINT {CONSTRAINT_NAME},
ADD {COLUMN_NAME_n} {TYPE} {NULL|NOT NULL} CONSTRAINT {CONSTRAINT_NAME};
Add a column with the NOT NULL constraint to a table that already has data
Suppose if you add a column (with NOT NULL) in a table that already has data, then PostgreSQL will issued an error.
For example, let’s take an example that we have a ‘directors’ table which has data on it and we want to add ‘contact_number’ column in that.
ALTER TABLE directors
ADD COLUMN contact_number INT NOT NULL;
then it will throw an error:
ERROR: column "contact_number" contains null values
SQL state: 23502
This is because the contact_number
column has the NOT NULL constraint. When PostgreSQL added the column, this new column receive NULL
, which violates the NOT NULL
constraint.
To solve this problem, add the column without the NOT NULL
constraints and then update the values in the contact_number
column.
Finally, set the NOT NULL
constraint for the contact_n
umber column.
Adding column using pgAdmin4
To add a column by using pgAdmin, follow the below steps.
Select the provided table to add a column and then right click on the Column.
Now click on the Create option and then click to Column.
![](https://www.pickupbrain.com/wp-content/uploads/2021/05/Screenshot1-1.png)
In the next step, provide the column name. Let’s create a column with ‘actors_age’ column name.
![Step2-adding column in existing table](https://www.pickupbrain.com/wp-content/uploads/2021/05/Screenshot-2-2.png)
Under the Definition, select the datatype from the drop-down list, you can also provide length of the datatype as well.
![Step3-adding column in existing table](https://www.pickupbrain.com/wp-content/uploads/2021/05/Screenshot-3-1.png)
You can also add Constraints if you need. Here I have set the default value as 0.
![Step4-adding column in existing table](https://www.pickupbrain.com/wp-content/uploads/2021/05/Screenshot-4.png)
Now click on the SAVE button, to create it.
![Step5-adding column in existing table](https://www.pickupbrain.com/wp-content/uploads/2021/05/Screenshot-5.png)
In PostgreSQL you cannot define the new column’s place in the table, it will append it to the end of the table.
![](https://www.pickupbrain.com/wp-content/uploads/2024/09/CP-Gupta-photo.png)
C P Gupta is a YouTuber and Blogger. He is expert in Microsoft Word, Excel and PowerPoint. His YouTube channel @pickupbrain is very popular and has crossed 9.9 Million Views.