PostgreSQL ADD COLUMN Statement to Add One or Multiple Columns To a Table

If you have been looking for a tutorial on the ALTER TABLE command in Postgres, your search ends here. This statement changes the definition of an existing table. In this article, we will be focusing on the ALTER TABLE ADD COLUMN syntax, which narrows down the functionality of ALTER TABLE to a single action: adding a column. We will walk you through different ways of adding a column using the command line, and also, a convenient GUI tool for PostgreSQL.

Contents

• PostgreSQL ADD COLUMN Statement Basics
  • Adding a New Column Using ALTER TABLE Command
  • Adding Multiple Columns with ALTER TABLE Statement
• Adding a VARCHAR Column with a NOT NULL Constraint
• Adding a Column With a Default Value
• Adding a Column IF NOT EXIST
• Adding Columns with Constraints
• Using PostgreSQL GUI Client – dbForge Studio for PostgreSQL
• Conclusion

PostgreSQL ADD COLUMN Statement Basics

Adding a New Column Using ALTER TABLE Command

There are several ways to add a column to a PostgreSQL table that already exists. The most basic syntax is provided below:

ALTER TABLE table_name
ADD COLUMN new_column_name data_type constraint;

Here, the table_name placeholder represents the name of the table you are willing to change. Respectively, new_column_name determines the name of the column. Data_type and constraint speak for themselves.

Note:
PostgreSQL adds a column at the end of the table as there is no option to specify its position.

To make it more visual, let us demonstrate the above steps in practice using SQL Shell. Suppose we have a demo database with the tickets table inside. To view the table use the \d tablename command:

Now, we are going to add a new_column with the character datatype to the tickets table by using the
ALTER TABLE tickets ADD COLUMN new_column CHAR; command.

If SQL Shell returns ALTER TABLE, the command was completed successfully.

Use the \d tablename command again to see the newly added column.

Adding Multiple Columns with ALTER TABLE Statement

To add several columns to a table simultaneously, you can use the same command. However, the syntax will be slightly different:

ALTER TABLE table_name
ADD COLUMN column_name1 data_type constraint,
ADD COLUMN column_name2 data_type constraint,
...
ADD COLUMN column_nameN data_type constraint;

In the example above, you can add as many columns, as you wish by separating each ADD COLUMN clause with a comma. However, the last one should end with a semicolon.

In practice, adding multiple columns to a table will look somehow like this:

Adding a VARCHAR Column with a NOT NULL Constraint

In this section of the article, we are going to focus on how to create a VARCHAR column with a NOT NULL constraint. First of all, let us define the terminology.

1. There are two character data types: CHAR and VARCHAR:

• CHAR [(n)] – fixed-size string data where n defines the size of the string in bytes and must be between 1 and 8000.
• VARCHAR [(n | max)] – string data of variable size. Use n to specify the size of the row in bytes, or use max to specify the limit for the column size up to the maximum storage size (2 GB).

2. The NOT NULL constraint is used to ensure that a given column of a table is never assigned the null value.

Earlier, we looked into the basic ALTER TABLE ADD COLUMN syntax.

ALTER TABLE table_name
ADD COLUMN new_column_name data_type constraint;

The same logic applies here. We just need to change the placeholders with the actual table name, new column name, data type, and constraint.

As a result, we have the following query:

ALTER TABLE tickets
ADD COLUMN new_column1 VARCHAR NOT NULL;

Adding a Column With a Default Value

If you are adding a column to a table that already contains some data, make sure to specify the default value for the new column. Otherwise, you will get the following error: column “new_column1” of relation “tickets” contains null values.

Adding a Column IF NOT EXIST

Another useful option to use in the ADD COLUMN clause is IF NOT EXISTS.

ALTER TABLE tickets 
ADD COLUMN IF NOT EXISTS new_column1 VARCHAR(20);

This option gives PostgreSQL instructions to add the new column only in case the column name does not already exist in the table. If it does, you will receive a corresponding response. However, if PostgreSQL fails to find such a column in the table, it will create one. Both variants are illustrated below:

Adding Columns with Constraints

As we have mentioned in the previous sections of this article, you can specify the data type and constraint of the column while creating it. Now, we are going to direct our attention to the different types of constraints that are used in specific cases.

We have already mentioned the NOT NULL constraint, but there are many more to serve their unique purposes:

• Check can be considered the most generic one among the other constraint types. It allows specifying that the value in a certain column must satisfy a Boolean (truth-value) expression.
• Unique constraint ensures that the data contained in a column, or a group of columns, is unique among all the rows in the table.
• With a primary key constraint, you can use a column as a unique identifier for rows in the table.
• A foreign key specifies that the values in a column must match the values appearing somewhere in another table. This maintains the referential integrity between two related tables.
• Exclusion ensures that if any two rows are compared on the specified columns or expressions using the specified operators, at least one of these operator comparisons will return false or null.

Feel free to refer to the official PostgreSQL website for more details, examples, and syntax.

Using PostgreSQL GUI Client – dbForge Studio for PostgreSQL

dbForge Studio for PostgreSQL is a GUI tool and universal utility for PostgreSQL database development and management. This PostgreSQL IDE allows users to create, develop, and execute queries, edit and adjust the code to their requirements in a convenient and user-friendly interface.

Particularly, it will be just as useful for column creation as a regular command line, but a lot more fun to operate. dbForge Studio is equipped with code auto-completion and syntax check. Therefore, when you start entering an ALTER TABLE ADD COLUMN query, the tool anticipates your next steps and offers you the most relevant variants:

In this example, we are creating a window_seat column in the seats table. Once you enter the corresponding query, click . After that, the message should be returned.

On refreshing Database Explorer, you will see the newly created column at the end of the list.

Conclusion

To sum up, the process of adding a column in PostgreSQL is not too complicated. However, it usually requires being familiar with SQL commands. The command-line tools are more oriented toward experienced tech-savvy users who are familiar with SQL coding. On the contrary, dbForge Studio for PostgreSQL comprises both a convenient graphical interface and an improved console.