Search code examples
sqldjangopostgresqlsql-inrawsql

Passing Optional List argument from Django to filter with in Raw SQL

When using primitive types such as Integer, I can without any problems do a query like this:

with connection.cursor() as cursor:
    cursor.execute(sql='''SELECT count(*) FROM account 
        WHERE %(pk)s ISNULL OR id %(pk)s''', params={'pk': 1})

Which would either return row with id = 1 or it would return all rows if pk parameter was equal to None.

However, when trying to use similar approach to pass a list/tuple of IDs, I always produce a SQL syntax error when passing empty/None tuple, e.g. trying:

with connection.cursor() as cursor:
    cursor.execute(sql='''SELECT count(*) FROM account 
        WHERE %(ids)s ISNULL OR id IN %(ids)s''', params={'ids': (1,2,3)})

works, but passing () produces SQL syntax error:

psycopg2.ProgrammingError: syntax error at or near ")"
LINE 1: SELECT count(*) FROM account WHERE () ISNULL OR id IN ()

Or if I pass None I get:

django.db.utils.ProgrammingError: syntax error at or near "NULL"
LINE 1: ...LECT count(*) FROM account WHERE NULL ISNULL OR id IN NULL

I tried putting the argument in SQL in () - (%(ids)s) - but that always breaks one or the other condition. I also tried playing around with pg_typeof or casting the argument, but with no results.

Notes:

  • the actual SQL is much more complex, this one here is a simplification for illustrative purposes
  • as a last resort - I could alter the SQL in Python based on the argument, but I really wanted to avoid that.)
Solution

  • At first I had an idea of using just 1 argument, but replacing it with a dummy value [-1] and then using it like

    cursor.execute(sql='''SELECT ... WHERE -1 = any(%(ids)s) OR id = ANY(%(ids)s)''', params={'ids': ids if ids else [-1]})

    but this did a Full table scan for non empty lists, which was unfortunate, so a no go.

    Then I thought I could do a little preprocessing in python and send 2 arguments instead of just the single list- the actual list and an empty list boolean indicator. That is

    cursor.execute(sql='''SELECT ... WHERE %(empty_ids)s = TRUE OR id = ANY(%(ids)s)''', params={'empty_ids': not ids, 'ids': ids})

    Not the most elegant solution, but it performs quite well (Index scan for non empty list, Full table scan for empty list - but that returns the whole table anyway, so it's ok)

    And finally I came up with the simplest solution and quite elegant:

    cursor.execute(sql='''SELECT ... WHERE '{}' = %(ids)s OR id = ANY(%(ids)s)''', params={'ids': ids})

    This one also performs Index scan for non empty lists, so it's quite fast.