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;

Answer 1

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

Answer 2

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

Answer 3

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