How to use postgresql variable in where clause

Question

-- It works! 
select * from device where device.user_id in (select user_id from user);
-- It doesn't work! 
select user_id into test from user;
select * from device where device.user_id in test;

Why does the first one work but second?

If I want to use a variable in where clause like the above, how can I do that?

• ypercubeᵀᴹ: (select user_id from user) gives you a whole result set, not a single value. Do you see why the second method won't do the same as the 1st?
• Yavuz Selim: You could do this with DO block but DO block does not output select. So you need to use function.

Answer 1

Actually select user_id from user will result in an error, because user is a reserved keyword (function) that returns the current user - and that does not have a column named user_id. See here: https://rextester.com/EPWT79333

The reason why it works in the sub-select of the first query, is that any column not available in the tables of the sub-query will resolve to a column from the outer query.

So the query:

select * 
from device 
where device.user_id in (select user_id from user);

Is actually the same as:

select * 
from device 
where device.user_id = user_id;

If you do have a table named user you have to quote it, to make clear that you mean the table, not the built-in function: "user"

So the correct form for the first query is:

select * 
from device 
where device.user_id in (select user_id from "user");