How to add column to an existing table in PostgreSQL?

PostgreSQLDatabaseData Storage

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 −

serial_nonameroll_nomarks_obtainedperc_marksmax_marksdate_of_entry
1Yash2642421002021-01-30
2Isha5617587.52002021-01-30

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 

serial_nonameroll_nomarks_obtainedperc_ marksmax_ marksdate_ of_ entrysubject
1Yash2642421002021-01-30[null]
2Isha5617587.52002021-01-30[null]

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

serial_nonameroll_nomarks_obtainedperc_ marksmax_ marksdate_ of_ entrysubject
1Yash2642421002021-01-30Maths
2Isha5617587.52002021-01-30[null]

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 

serial_nonameroll_nomarks_obtainedperc_ marksmax_ marksdate_ of_ entrysubject
1Yash2642421002021-01-30Science
2Isha5617587.52002021-01-30Science
raja
Published on 02-Feb-2021 16:16:18
Advertisements