Copy rows of data from one table to another table in same database using pgadmin

Question

I'm quite new to this and I need some help.

I would like to copy rows of data from one table to another table in the same database using pgadmin. However, they have slightly different column id. Is there a way to write a script to match them and do a copy?

Please help. Thank you.

I have tried the following( to copy existing data in STUD table into STUDENT table):

CREATE TABLE STUDENT(
Student_id  INT,
Student_name TEXT,
Student_address TEXT
);

INSERT INTO STUDENT
SELECT * FROM STUD AS D
WHERE(
Student_id = D.id,
Student_name = D.name
);

I have 2 tables STUDENT and STUD. Under STUDENT table, I have Student_id, Student_name and Student_address. Under STUD table, I have name and id. The rows in the both the tables are not in order.

** ADD ON**
The table in STUD does not come with the Student_address column. Only the STUDENT table has. Since STUD does not have Student_address, I would like to put Student_address in STUDENT table as NULL. Is it possible to write a general script where columns in STUDENT table that does not exist in Stud will be NULL?

EDIT** Instead of NULL, I am required to insert "TBC"

Sounds like you'll need to use an alias. Google PostgreSQL Alias. It takes the form of < select colid as differentid from table >. If different data type then use a cast. — RThomas
– RThomas, Commented Mar 7, 2017 at 3:19
Posting a code sample of what you tried will also help people understand your intent better as well as demonstrate effort on your part. — RThomas
– RThomas, Commented Mar 7, 2017 at 3:25
What is that where statement supposed to do? You can't "join" between the target table and the source table. Are you maybe trying to update existing rows instead? — user330315
– user330315, Commented Mar 7, 2017 at 6:14
I am trying to copy existing data in STUD into STUDENT table. — user7609786
– user7609786, Commented Mar 7, 2017 at 6:22

score 1 · Accepted Answer · 2017-03-07 07:44:55Z

1

Use an insert based on a select:

insert into student (Student_id, Student_name)
select id, name
from stud;

The column names don't have to match, only the data types. Any column you do not specify in the insert's target list will be set to null (or more specifically to the default value defined for that column)

Online example: http://rextester.com/ISCL45721

Edit (after the requirements have changed)

To insert a constant value into one column for all rows, include that constant in the select clause:

insert into student (Student_id, Student_name, student_address)
select id, name, 'TBC'
from stud;

edited Mar 7, 2017 at 7:44

answered Mar 7, 2017 at 6:01

user330315

Sign up to request clarification or add additional context in comments.

5 Comments

user7609786 Over a year ago

The table in Stud does not come with the address column. Only the Student table has. As there is no match, I would like to put address as NULL. Is it possible?

user330315 Over a year ago

Then simply don't specify it.

user7609786 Over a year ago

There are changes. I am required to insert "TBC" instead of NULL for those column that exists in STUDENT but does not exists in STUD. Please help x.x

user330315 Over a year ago

See my edit. In general it's not considered polite to change your question after every bit of information you get.

user7609786 Over a year ago

Thank you very much! I'm so sorry I edited it for so many times. No more edit.

Collectives™ on Stack Overflow

Copy rows of data from one table to another table in same database using pgadmin

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related