How to insert values from one table into another in PostgreSQL?

Suppose you have two tables: marks and student_info. Examples are given below for the two respectively

Now, suppose your manager at work looks at the two tables and tells you, “Why do we have two tables? Simplify things, shift everything to one table!”

So you decide to add the perc_marks column to the student_info table.

ALTER TABLE student_info
ADD COLUMN perc_marks integer

Now, how will you populate this column? Will you manually add the marks for each column? That will leave the room open for a lot of errors and will also be very time-consuming. Instead, this is what you could do −

UPDATE student_info
SET perc_marks = marks.perc_marks FROM marks
WHERE student_info.roll_no = marks.roll_no

Here, the roll_no is used as the common field between the two tables and update values for marks in the student_info table. Please note that the assumption here is that there is only one entry for each roll_no in the marks table. If there are multiple rows having the same roll_no in the marks table, it will confuse PostgreSQL. Generally, a primary key/ foreign key is used as the common field, to avoid this confusion.

Now, if you query the student_info table (SELECT * from student_info), you will see the following output −

As you can see, the values of perc_marks from the marks table have been successfully added to student_info.

Yash Sanghvi

Updated on: 02-Feb-2021

2K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started