How to use postgresql variable in where clause # 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?
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");