I was trying to fetch distinct users with similar interests and stumbled upon this problem. Basically I wanted to do something like:
SELECT DISTINCT(uid)
FROM interest
WHERE (interest , score) IN (('family' , > 0.32), ('paris' , > 0.77));
I get that I can join multiple where statements to achieve this but this would be a more cleaner approach. Is this possible to do?
IN doesn't take operators. It takes scalar values/tuples or a subquery that returns scalar values/tuples. I think you want:
SELECT DISTINCT uid
FROM interest
WHERE (interest = 'family' AND score > 0.32) OR
(interest = 'paris' AND score > 0.77);
You could express this as:
SELECT DISTINCT i.uid
FROM interest i JOIN
(VALUES ('family', 0.32), ('paris', 0.77)
) v(interest, threshold)
ON i.interest = v.interest and i.score > v.threshold
You are trying to use "tuples".
In PostgreSQL tuples can work with equlities or inequalities. In either case ALL values will participate either in the equality or inequality. You cannot mix the operation for different parts of the tuple as you are trying to do: equality for the first element, inequality for the second one. Therefore, in this case you won't be able to use the tuples.
The solution is much simpler. Just use a combined predicate with the OR operator, as in:
SELECT DISTINCT(uid)
FROM interest
WHERE interest = 'family' and score > 0.32
or interest = 'paris' and score > 0.77;