postgres where equals any value

Question

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

tinazmu · Accepted Answer · 2022-11-27 01:11:38Z

2

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

answered Nov 27, 2022 at 1:11

tinazmu

5,5442 gold badges12 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

J_H · Accepted Answer · 2022-11-27 01:05:05Z

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.