Question 180011 in PostgreSQL Question posted in
The official documentation can be found here.

How do I filter records in Postgresql using “in” and “and” operators?

ID name isSearchable
1 foo true
2 bar true
3 zar false

I’ve got some ids and I need to filter records where they have isSearchable = true.

This query give as result ID = 1 because is searchable, but I would to apply the filter isSearchable to the entire result, not row-by-row.

SELECT *
FROM my_table
WHERE id IN (1, 3)
  AND isSearchable = true

So in this case I’m expecting no-results because both records should be in first isSearchable and after that, filter the ids.

I’ve tried experimenting with sub-query etc but the in operator (or the or operator) but I’m not able to accomplish the result.

Maybe is something really simple, but I’ve no ideas on how to solve.
Thanks for your help.

Tags:

2

Answers


  1. 0

    How about this?

    SELECT *
FROM (
    SELECT *
    FROM my_table
    WHERE id IN (1,3)
) x
WHERE isSearchable = true;

    Think of a generic programming language:

    jshell> Integer x;
x ==> null

jshell> if(x == 3 || x == null) System.out.println("bad");
|  Exception java.lang.NullPointerException: Cannot invoke "java.lang.Integer.intValue()" because "REPL.$JShell$11.x" is null
|        at (#2:1)

jshell> if(x == null || x == 3) System.out.println("bad");
bad

    The filter order can completely change the results. If you explicitly write the query so that the execution plan is not open to interpretation, you’re good.

    Login or Signup to reply.
  2. 0

    One approach using a window function:

    SELECT ID 
FROM (SELECT ID,
             MIN(isSearchable::INT) OVER() AS minSearchable
      FROM my_table
      WHERE id IN (1,3)) cte 
WHERE minSearchable = 1

    Check the demo here.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search