CODEWITHSUNDEEP
sqlpostgresqlobjection.js
Brook MG
Brook MG

Reputation: 671

Using sql where in operator with numbers

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?

Upvotes: 0

Views: 93

Answers (2)

The Impaler
The Impaler

Reputation: 48800

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;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269883

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

Upvotes: 3

Related Questions