SQL Add Column: A How-To Guide with Examples

Do you need to use SQL to add a column to your table?

It’s easy to do once you know the command and the syntax.

Let’s take a look at how to do it in several different databases and see some examples.

 

SQL Add Column

To add a column to a table in SQL you use the ALTER TABLE command. Specifically, you write it as ALTER TABLE ADD COLUMN.

This command lets you do many things, and one of those is adding a new column.

To add a column using SQL in Oracle, SQL Server, MySQL, and PostgreSQL, you can use the syntax shown here:

ALTER TABLE table_name
ADD [COLUMN] column_name data_type [constraint];

All of these four databases (Oracle, SQL Server, MySQL, and PostgreSQL) use the same SQL add column syntax.

So how do you use this statement?

• First, you specify the ALTER TABLE command.
• Then, in the place of “table_name”, you specify the table you want to add the column to.
• Then you use the keyword ADD
• For PostgreSQL, you need to add the word COLUMN. For other databases, it is optional.
• Then you specify the new column name where it says “column_name”
• You then specify a data type for this column, which must be one of the data types available in your database.
• You can then optionally specify a constraint, such as NOT NULL.

Let’s see some examples of adding a new column using SQL.

 

Oracle SQL Add Column Example

Adding a column to a table in Oracle is similar to the other databases. The main difference is with the data types you can use.

For example, to add a text column to a customer table, the statement could use a VARCHAR2 data type:

ALTER TABLE customer
ADD suburb VARCHAR2(100);

You can specify a constraint, such as NOT NULL:

ALTER TABLE customer
ADD suburb VARCHAR2(100) NOT NULL;

You can also specify a default value:

ALTER TABLE customer
ADD suburb VARCHAR2(100) DEFAULT 'Central';

You can add multiple columns in a single ALTER TABLE command as well. You’ll just need to surround the column details in brackets, and separate them with commas. This is slightly different from other databases which don’t require the brackets.

ALTER TABLE customer
ADD (
  suburb VARCHAR2(100),
  postcode VARCHAR2(20)
);

You can specify different data types, depending on the type of column you want to add. For example, to add a numeric value you may want to use the NUMBER data type.

ALTER TABLE customer
ADD credit_value NUMBER(10);

While we’re here, if you want an easy-to-use PDF guide for the main features in different database vendors, get my SQL Cheat Sheets here:

 

SQL Server Add Column Example

To add a column to a table in SQL Server, the ALTER TABLE statement is used in a similar way to other databases.

For example, adding a text column to a customer table is done like this:

ALTER TABLE customer
ADD suburb VARCHAR(100);

You can also specify constraints after the data type, such as NOT NULL:

ALTER TABLE customer
ADD suburb VARCHAR(100) NOT NULL;

To add multiple columns to a table in a single command, you specify the ADD keyword and column details again:

ALTER TABLE customer
ADD suburb VARCHAR(100),
ADD postcode VARCHAR(20);

You can add a numeric value to a table in SQL Server as well. Just replace the data type with the type you want to use:

ALTER TABLE customer
ADD credit_value INT;

 

MySQL Add Column Example

To add a new column to a table in MySQL, you use the ALTER TABLE statement in a similar way to other databases.

For example, to add a text value to a customer table:

ALTER TABLE customer
ADD COLUMN suburb VARCHAR(100);

The keyword COLUMN is actually optional. This statement will do the same thing:

ALTER TABLE customer
ADD suburb VARCHAR(100);

You can also specify constraints after the data type, such as NOT NULL:

ALTER TABLE customer
ADD suburb VARCHAR(100) NOT NULL;

To add multiple columns to a table in a single command, you specify the ADD keyword and column details again:

ALTER TABLE customer
ADD suburb VARCHAR(100),
ADD postcode VARCHAR(20);

You can add other data types, such as numbers as well.

ALTER TABLE customer
ADD credit_value INT;

 

PostgreSQL Add Column Example

Adding a new column in PostgreSQL is similar to other databases. The main difference is that the keyword COLUMN needs to be used after the ADD keyword.

For example, to add a text value to a customer table:

ALTER TABLE customer
ADD COLUMN suburb VARCHAR(100);

You can also specify constraints after the data type, such as NOT NULL:

ALTER TABLE customer
ADD suburb VARCHAR(100) NOT NULL;

To add multiple columns to a table in a single command, you specify the ADD keyword and column details again:

ALTER TABLE customer
ADD suburb VARCHAR(100),
ADD postcode VARCHAR(20);

Adding a numeric column to the table is done in the same way:

ALTER TABLE customer
ADD credit_value INTEGER;

 

Conclusion

The ALTER TABLE statement is used in Oracle, SQL Server, MySQL, and PostgreSQL to do many things to a table, and one of those things is adding a new column. You do this by specifying ALTER TABLE ADD COLUMN. You can specify the name, data type, and any additional constraints you want to put on the column.

While we’re here, if you want an easy-to-use PDF guide for the main features in different database vendors, get my SQL Cheat Sheets here: