The Postgres database stores data in the form of tables and the table columns specify an attribute for all the database entries. Sometimes we want to insert a column to a table that already exists. This is possible in Postgres. To insert/add a column to a table the ALTER statement is used. To define a column while creating, we need to provide the column’s name and the data type of that particular column. The same two parameters need to be defined while adding a column to an existing table in Postgres.
This tutorial will demonstrate the methods to add columns to a Postgres table. So let’s get into the further details.
How to Add Columns to a Postgres Table?
We can add/insert a column to a pre-existing Postgres table, using an ALTER statement. The syntax to add a column to a table is given as:
ALTER TABLE t_name
ADD COLUMN c_name dataType;
Syntax Description
- The ALTER TABLE statement is used to alter the specified table.
- The table name is entered after the ALTER TABLE statement.
- The ADD COLUMN clause adds a column to an existing/specified Postgres table.
- The column’s name and its data type need to be specified after the ADD COLUMN statement.
Let’s see how to insert a new data column into a Postgres table.
Example: Add Columns to a Table in PostgreSQL
Let’s add a column to the “account_details” table. The name of the column will be “passcode” and its data type will be “VARCHAR”. The query can be written as follows:
ALTER TABLE account_details
ADD COLUMN passcode VARCHAR;
This will result in the creation of a new column in the table.
This means that the column “passcode” has been added to the table. We will verify from the side panel. In the side panel, expand the table option in which you have added the column, expand the columns option there you will be able to see your new column. If not, refresh the table from the drop-down menu that will appear after you right-click on the table.
This is how we can add a new column to an existing table with the specific data type. In the same way, we can insert/add multiple columns to the Postgres table.
Example 2: Add Columns With Constraints to a Table in PostgreSQL
We can also add some constraints to the column of a database table. The most commonly used constraints for the table column are; NOT NULL, UNIQUE, and DEFAULT.
Step 1: Add the Column with the Constraint
The basic syntax to add a column with a constraint is given as:
ALTER TABLE t_name
ADD COLUMN c_name dataType constraints;
The specific constraint will be mentioned after the data type declaration, the rest of the query remains the same.
Let’s suppose we want to add the “passcode” column with a NOT NULL constraint we will write the following query:
ALTER TABLE account_details
ADD COLUMN passcode VARCHAR NOT NULL;
The table will be successfully altered for the addition of a new column “passcode” with VARCHAR data type and NOT NULL constraint.
Step 2: Verify the Added Column With the Constraint
We can see the constraint of the table column from the side panel. Go to the column of the table in which you have added the constraint. Right-click it and choose Properties from the drop-down menu that appears.
A window displaying the column’s properties will appear on the screen.
Navigate to the constraints tab to see the constraints added to the column.
We can see that the NOT NULL constraint is toggled on which means that the constraint has been added.
Note: We can also see the constraint of the table column by executing the “\d tab_name” query in psql after connecting to the database in which this particular table exists.
Add Columns to a Table in PostgreSQL Manually
We can also manually add a column in a table. This GUI method is easy to implement. Let’s see how we can add a column to the table manually in pgAdmin.
Step 1: Add the Column With the Constraint
To manually add a column to the table, select the table in which you want to add a column. Right-click on the “columns” option under that table. Select the “Create” option from the drop-down menu that appeared and then choose the “Column” option.
A window will be shown on the screen to create a table column.
We can define the data type of the column in the “definition” tab of this window like this:
We will select “character Varying” from here.
From the “constraints” tab, you can add constraints to the columns. The options provided as constraints can be seen in the Constraints tab:
I have enabled the “NOT NULL?” constraint for this column as seen in the above image. We can also insert some default values etc. into the column.
We have added the constraints, and variables to the column. We can also generate the SQL query for the addition of the customized column. To add a new data column in a Postgres table, click the “Save” button.
Step 2: Verify the Added Column with Constraint
To verify the new column, we can see the side panel where the new column “address” has immediately been added under the columns option.
We can also verify the columns of the table by executing the SELECT statement like this:
SELECT * FROM account_details;
This query will output the selected table i.e. account_details.
In this way, we can insert/add columns to a pre-existing table in Postgres.
Conclusion
We can add a column in an already existing table, by two methods. The first method is to use the ALTER statement and secondly, we can do this manually in the pgAdmin. Both approaches are simple to implement for the user. In this tutorial, we have seen a couple of ways to add a column to a table in PostgreSQL with their proper implementation.
