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_number 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.
In the next step, provide the column name. Let’s create a column with ‘actors_age’ column name.
Under the Definition, select the datatype from the drop-down list, you can also provide length of the datatype as well.
You can also add Constraints if you need. Here I have set the default value as 0.
Now click on the SAVE button, to create it.
In PostgreSQL you cannot define the new column’s place in the table, it will append it to the end of the table.