Alter Table Add Column - How to Add a Column in SQL
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;
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.
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:
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;
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:
The output appears as follows:
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 | | |
"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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started