How to make IN expression an optional parameter if empty list of elements was provided?

Question

I would like to make IN statement an optional part of my sql query if empty list was provided as argument, but failed to to it. I can do workaround and use some default value in code instead of empty list (all cam_ids), but I would like to know how to do it properly.

I have the following sql expression (real expression is much longer):

SELECT 
 id, cam_id
 FROM sometable
 WHERE id > %(_id)s
 // if use = instead of IN, it works well (of course if cameras is just one value, not array)
 AND (%(camera)s is NULL OR cam_id IN %(camera)s)

In python I provided arguments to the query the following way:

values = {"_id": 10, camera: tuple(1, 2, 3)]}
curs.execute(query, values)

Everything works if tuple is not empty, otherwise:

if camera = None, I got the following error:

psycopg2.errors.SyntaxError: syntax error at or near "NULL" LINE 6: WHERE (NULL is NULL OR cam_id IN NULL)

if camera = tuple(), I got the following error:

psycopg2.errors.SyntaxError: syntax error at or near ")" LINE 6: WHERE (() is NULL OR cam_id IN ())

In order to be more clear:

I would like to get all result for all possible cam_ids if empty array was provided, so the results should be identical to SELECT * FROM tablename;

Edit: for Maurice Meyer

I have found the following problems when tried to make bigger queries

select * from vworker_tracks
where
    // still need some default value: cam_id != ''
    (cam_id = any('{}') or cam_id != '') 
and
    // unexpected results when both are true, provides all tracks  > 0.0
    (track_duration_seconds = 2.5 or track_duration_seconds > 0.0) 
and 
    id < 100

order by id desc limit 10;

Maurice Meyer · Answer 1 · 2021-06-07T20:42:11.897

1

You could use OR in your WHERE condition to 'emulate' if/else:

query = """
with cameras as (
    select
        %(ids)s::int[] as ids,
        %(idsLen)s as count /* simplify */
)
select
count(conversations.*)
from
conversations,
cameras
where
(
(cameras.count >= 1 and id = ANY(cameras.ids))
or
(cameras.count < 1 and id > %(_id)s)
)
"""

for ids in [[81, 60], []]:
    values = {"_id": 10, "ids": ids, "idsLen": len(ids)}
    curs.execute(query, values)
    print(curs.fetchone())

Out:

(2,)    # 2 ids given, returns 2 records
(118,)  # empty array, returns ALL records

edited Jun 07 '21 at 20:42

answered May 17 '21 at 23:32

Maurice Meyer

17,279
4
30
47

I tried the provided code it works, but I still see some limitations for bigger queries, it still need to have some "default" value to provide all values (disable filter) on the right side of `OR` statement. If both left an right sides of 'OR' are true, I am provided with "all" results, but I expect only left part of 'OR' statement. I added what I see in edit part to my answer, please check the Edit. Is it possible to solve the described issues? – Artiom Kozyrev May 18 '21 at 15:37
@Artiom Kozyrev: Rewrite the where condition that either **one** operand is true. Why not executing 2 different queries in Python ? – Maurice Meyer May 18 '21 at 18:17
The full query has several `x in ARRAY` conditions, if the ARRAY is empty the `X in ARRAY` statement should be completely ignored and have no effect. So since there are several `x in ARRAY` conditions it could be several combinations of active and inactive statements and it means that I should write several sql queries in Python. It is really long work and the way is error prone. So I am searching for some native solution, something like `AND (%(camera)s is NULL OR cam_id = %(camera)s)`, when no cam_id means no effect of the statement. – Artiom Kozyrev May 18 '21 at 19:02

score 0 · Accepted Answer · answered May 20 '21 at 11:48

The answer does not provide any native sql solution, it is based on https://use-the-index-luke.com/sql/myth-directory/dynamic-sql-is-slow, follows KISS principle:

from aiopg.cursor import Cursor


async def my_test_query(curs: Cursor, params: dict) -> list:
    """
    the snippet demonstrates how to create IN filters
    The idea is build on the article
    https://use-the-index-luke.com/sql/myth-directory/dynamic-sql-is-slow
    follows KISS principle
    """
    query = f"""
        SELECT 
          id,
          cam_id,
          track_duration_seconds,  
          top_color, 
          bottom_color,
          crossed_lines->>'entrance',
          crossed_lines->>'cross_direction'
        FROM vworker_tracks 
        WHERE id < %(_id)s
    """
    if params.get("camera", None) is not None:
        query += " AND cam_id IN %(camera)s"

    if params.get("bottom_color", None) is not None:
        query += " AND bottom_color IN %(bottom_color)s"

    if params.get("top_color", None) is not None:
        query += " AND top_color IN %(top_color)s"

    if params.get("cross_direction", None) is not None:
        query += " AND crossed_lines->>'cross_direction' IN %(cross_direction)s"

    if params.get("entrance", None) is not None:
        query += " AND crossed_lines->>'entrance' IN %(entrance)s"

    query += " ORDER BY id DESC LIMIT 50;"

    await curs.execute(query, params)
    res = await curs.fetchall()
    return res

score -1 · Answer 3 · answered May 17 '21 at 19:06

-1

You can use =ANY() in PostgreSQL, where ANY takes an array as input. And that array can be empty, no problem. Simpel example:

SELECT  1 = ANY('{}') -- false
    ,   2 = ANY('{2,3,4}'); -- true

answered May 17 '21 at 19:06

Frank Heikens

117,544
24
142
135

Hello, I tried to do `SELECT * FROM table_name WHERE cam_id = ANY('{}') ;` and got no results, instead I would like to get all possible cam_ids if empty array was provided. Sorry if it probably was not really clear from the question, but in case of empty array, I would like to get everything, like from `SELECT * FROM table_name;` – Artiom Kozyrev May 17 '21 at 19:25

How to make IN expression an optional parameter if empty list of elements was provided?

3 Answers3