Select where in multiple pairs - postgresql-CodePudding

I want to find all rows that match a list of pairs (multiple columns) and my query is not working. I get results if I only have a list of either column, but not when they are paired.

SELECT * FROM table
WHERE (name,age) IN (('john',23),('jane',50),('john',44))

I saw posts about using EXISTS but I don't know how to convert this query.

I checked my database and it should return 8 rows but it is returning an empty table

CodePudding user response：

You should use as explained below:

SELECT * 
FROM t
WHERE (col1, col2) 
       IN ( VALUES (val1a, val2a), (val1b, val2b)) ;

Your Query would be:-

SELECT * FROM t
WHERE (name, age) IN ( VALUES ('john',23),('jane',50),('john',44));

CodePudding user response：

I checked my database and it should return 8 rows but it is returning an empty table

Your check is wrong. The query is doing what you intend.

One possibility is that the names are incorrect because they have additional characters -- perhaps hidden. You can easily check this. Does this return anything?

SELECT *
FROM table
WHERE name = 'john';

If this returns no rows and you think it should, then you need to figure out what is going wrong. Does this condition work?

WHERE name LIKE '%john%'

The issue could be spaces at the beginning of the name, hidden characters, or look-alike characters.

If the first query does return rows, then the problem could be with age. Does this return anything?

SELECT *
FROM table
WHERE name = 'john' AND age = 23;

A problem with age is more subtle, but one possibility is a floating point representation, where the value is really 22.9999999997 rather than 23.