PostgreSQL subqueries as values-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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 *