postgres where equals any value-CodePudding

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)?

CodePudding user response：

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.

CodePudding user response：

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