PostgreSQL subqueries as values

Question

I am trying to use a postgreSQL INSERT query with a subquery as parameter value. This is to find the corresponding user_id from an accompanying auth_token in user_info tabel first and then create a new entry in a different table with the corresponding user_id.

My query looks something like this

INSERT INTO user_movies(user_id, date, time, movie, rating)
VALUES ((SELECT user_id FROM user_info where auth_token = $1),$2,$3,$4,$5)
RETURNING *

I know that a query such as this will work with a single value

INSERT INTO user_movies(user_id)
SELECT user_id FROM user_info where auth_token = $1
RETURNING *

but how do I allow for multiples input values. Is this even possible in postgreSQL.

I am also using nodejs to run this query -> therefore the $ as placeholders.

Answer 1

To expand on my comment (it is probably a solution, IIUC): Easiest in this case would be to make the inner query return all the values. So, assuming columns from the inner query have the right names, you could just

INSERT INTO user_movies(user_id, date, time, movie, rating)
SELECT user_id,$2,$3,$4,$5 FROM user_info where auth_token = $1
RETURNING *

Note this form is also without VALUES, it uses a query instead.

Edited 20220424: a_horse_with_no_name removed the useless brackets around SELECT ... that appeared in my original version; thanks!

Answer 2

YOu could try uising where IN clause

INSERT INTO user_movies(user_id)
SELECT user_id 
FROM user_info
WHERE auth_token IN ($1,$2,$3,$4,$5)
RETURNING *