How to insert values from a select query-CodePudding

how do I insert the std_id value and sub_id value in the student_subject table

insert into student_subjects(student_id,subject_id)
            values(std_id,(select id from subjects 
                           where guid in 
                           (select * from 
                           unnest(string_to_array(subjects_colls,',')::uuid[])))::int);

ERROR:  more than one row returned by a subquery used as an expression

CodePudding user response：

Get rid of the values clause and use the SELECT directly as the source for the INSERT statement:

You also don't need to unnest your array, using = any() will be a bit more efficient (although I would recommend you do not pass comma separated strings, but an array of uuid directly)

insert into student_subjects(student_id,subject_id)            
select std_id, s.id 
from subjects s
where guid = any(string_to_array(subjects_colls,',')::uuid[])

I assume this is part of a procedure or function and std_id and subjects_colls are parameters passed to it.