I have two Table

one is student_record_temp

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.

CodePudding user response：

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';

CodePudding user response：

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;