Copy A table to another Table in PostgreSQL

Question

I have two Table

one is student_record_temp

id	name	roll
1	Shadman	2
2	Adnan	3

Now I want to copy the row in student_record where the name is shadman and it returns both student_record_temp and student_record id

I am trying to solve this in

INSERT INTO student_record (name,rool) select name,roll from student_record_temp 
FROM student_record_temp WHERE name='shadman' RETURNING student_record.id,student_record_temp.id

ERROR:  missing FROM-clause entry for table "student_record_temp"
LINE 8: student_record.id,student_record_temp.id

but I got an error and how I can solve this.

I do not understand the part you are saying "it returns both student_record_temp and student_record id" — mystery
– mystery, Commented Dec 26, 2021 at 5:01
you are inserting into student_record table so there is no such row in that table hence no id in student_record table — mystery
– mystery, Commented Dec 26, 2021 at 5:04

milad gorjizadeh · Accepted Answer · 2021-12-26 05:22:06Z

1

To copy a table with partial data from an existing table

CREATE TABLE new_table AS SELECT * FROM existing_table WHERE condition; The condition in the WHERE clause of the query defines which rows of the existing table will be copied to the new table

or you can use this:

INSERT INTO TABLE1 (id, col_1, col_2, col_3) SELECT id, 'data1', 'data2', 'data3' FROM TABLE2 WHERE col_a = 'something';

answered Dec 26, 2021 at 5:22

milad gorjizadeh

111 bronze badge

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

Comments

Pooya · Accepted Answer · 2021-12-26 05:35:57Z

You can't be returning data from select in the insert query. You can use two scenarios:

second scenario If name column data from student_record_temp table is unique. you can use join in returning query:

insert into student_record as sr (name, roll)
select srt.name, srt.roll
from student_record_temp srt
where name = 'shadman'
returning sr.id, (select id from student_record_temp r_srt where r_srt.name = sr.name);

first scenario If name column data from student_record_temp table is not unique. you have to create temp unique field and use from that

with select_data as (
    select srt.id, srt.name, srt.roll, row_number() OVER (ORDER BY id) AS rn
    from student_record_temp srt
    where name = 'shadman'),
     add_data as (
         insert into student_record as sr (name, roll)
             select sd.name, sd.roll
             from select_data sd
             order by sd.id
             returning sr.id)
select ad.id as insert_id,
       sd.id as select_id
from (select id, row_number() over (order by id) as rn from add_data) ad
         inner join select_data sd on ad.rn = sd.rn;

Collectives™ on Stack Overflow

Copy A table to another Table in PostgreSQL

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related