How to add column to an existing table in PostgreSQL?

The syntax to add a new column to an existing table is quite straightforward.

ALTER TABLE table_name
ADD COLUMN column_name column_type column_constraint;

Say you have existing table marks. An example is given below −

Now, suppose you want to add a column named subject. You can do that using −

ALTER TABLE marks
ADD COLUMN subject VARCHAR;

Now if you query the table again using,

SELECT * from marks

You will see the following output  −

Note that the values in the subject column are null because we have just created the column, not populated it. We can populate it using the UPDATE statements. The statement syntax is as follows −

UPDATE table_name
SET column_name = column_value
WHERE condition

For instance, if, in the above example, Yash has scored 42 marks in Maths, the UPDATE statement will look like this −

UPDATE marks
SET subject 'Maths'
WHERE roll_no = 26

You could have also added the name=’Yash’ condition instead of roll_no=26. Now, if you query the table, you will see the following output −

If you don’t add any condition in the UPDATE statement, the value of every row will be changed for that column. For instance, if I run the following query −

UPDATE marks
SET subject = 'Science'

And then query the table, I’ll see the following output −

Yash Sanghvi

Updated on: 02-Feb-2021

824 Views

Kickstart Your Career

Get certified by completing the course

Get Started