Alter Table Add Column - How to Add a Column in SQL

Introduction

Database management involves regular alterations of the database structure which may include creating or dropping tables, adding columns, dropping columns, updating and deleting row data. Here we will look at one of the commands to alter the state of a database table by adding new columns into the table. ALTER TABLE is the command used to change the state of a table in a database. To insert a column in a table we use the command ALTER TABLE ADD COLUMN.

The basic syntax to add a column is as shown below;

ALTER TABLE tbl_name
ADD COLUMN col_name data_type;

Now let us analyze the syntax above for a better understanding.

The first statement ALTER TABLE precedes the specific table where you intent to insert a new column on. The second statement has the ADD COLUMN clause that precedes the name of the new column and the attributes associated with the new column. The attributes includes the data type, the default values. Whenever a new column is added into a table in postgres, it is appended at the end of that table. Currently, postgres does not provide an option to define the position of the new column as provided in other databases like oracle and MYSQL.

Additionally, it is possible to add multiple columns to a table at the same time. To achieve this, you need to add several ADD COLUMN statements separated by a comma after the ALTER TABLE clause as shown in the illustration below;


GeSHi Error: GeSHi could not find the language sl (using path /nas/content/live/orkbprod/wp-content/plugins/codecolorer/lib/geshi/) (code 2)

To start with, we will create a new table , teacher_details with two columns; teacher_id and teacher_name in our database using the following statement.

CREATE TABLE teacher_details (
   teacher_id SERIAL PRIMARY KEY,
   teacher_name VARCHAR NOT NULL
);

Then, let’s add the registration_number column to the teacher_details table using the following statement:

ALTER TABLE teacher_details
ADD COLUMN registration_number VARCHAR;

Now, let’s try to add multiple columns into the table. In this case, we shall add the class_id, the email, and the phone_number cloumns altogether. To do this we use the following statement;

ALTER TABLE teacher_details
 ADD COLUMN email VARCHAR,
ADD COLUMN class_id VARCHAR,
 ADD COLUMN phone_number VARCHAR;

The following statement is used to describe the table structure in postgresql:

#\d teacher_details

The output appears as follows:

articles=# \d teacher_details;
                                            Table "public.teacher_details"
       Column        |       Type        | Collation | Nullable |                       Default
---------------------+-------------------+-----------+----------+-----------------------------------------------------
 teacher_id          | integer           |           | not null | nextval('teacher_details_teacher_id_seq'::regclass)
 teacher_name        | character varying |           | not null |
 registration_number | character varying |           |          |
 email               | character varying |           |          |
 class_id            | character varying |           |          |
 phone_number        | character varying |           |          |
Indexes:
    "teacher_details_pkey" PRIMARY KEY, btree (teacher_id)

When using the lower versions of postgresql, you might find that adding columns that are NOT NULL returns an error. In such a case, you create the column first without the constraint NOT NULL, then update the column by setting the default value, finally you set the columns to NOT NULL. However, the issue has been solved in other newer versions and you can just create columns that are not nullable.

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.