0
# 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?

2
  • 1
    (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? Dec 19 '18 at 17:13
  • You could do this with DO block but DO block does not output select. So you need to use function. Dec 21 '18 at 7:32
6

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");

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.