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

postgres where equals any value – Postgresql

I have a query where I filter a column based on a where clause, like so:

select * from table where col = val

What value do I set for val so that there is no filtering in the where clause (in which case the where clause is redundant)?

Tags:

2

Answers


  1. 0

    What value do I set for val so that there is no filtering in the where clause?

    It’s impossible.

    You might instead use

        query = "SELECT *  FROM TABLE"
    if val is not None:
        query += "  WHERE col = :val"

    None is a common sentinel value,
    but feel free to use another.

    Consider switching from = equality to LIKE.
    Then a % wildcard will arrange for an unfiltered blind query.

        query = "SELECT *  FROM table  WHERE col LIKE :val"

    Pro:

    • You still get to exploit any index there might be on col, for values of val ending with a % wildcard.

    Cons:

    • The program behavior is clearly different, e.g. there might be a UNIQUE KEY on col, and the revised SELECT can now return multiple rows.
    • Your data might contain wildcard characters, which now need escaping.
    • Your users may have more flexibility now to pose queries that you didn’t want them to.
    • Any % characters that are not at the end of val may disable the index, leading to unexpectedly long query times / result set sizes.
    Login or Signup to reply.
  2. 0

    If col can’t be null you can use col=col?

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

Back To Top
Search