1

I need to run a raw query and the WHERE clause is an IN.

select
    o.*,
from
    ff_ooo as o
left join
    ff_ooostatus s on o.id = s.ooo_id
left join
    ff_rrrr r on s.rrrr_id=r.id
where
    o.id in %s
group by
    o.id
having
    r.due_date = max(r.due_date)

I execute it using 

return list(Ooo.objects.raw(
    sql,
    params=(ooo_ids,)
))

ooo_ids is an array of integers.

The resulting SQL fails with this error:

ProgrammingError: syntax error at or near "ARRAY" LINE 13: o.id in ARRAY[7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17]

I see that the Postgres ORM code is making the IN statement bound parameter into an ARRAY[]. but postgres DB does not like it.

How do I properly bind an integer array to an IN clause in a Django raw SQL query against a postgres DB?

Krystian Cybulski
  • 10,789
  • 12
  • 67
  • 98
  • 1
    For Postgresql this answer should help: http://stackoverflow.com/a/22008870/3246440 – timo.rieber Nov 14 '14 at 15:57
  • possible duplicate of [passing lists or tuples as arguments in django raw sql](http://stackoverflow.com/questions/6895051/passing-lists-or-tuples-as-arguments-in-django-raw-sql) – timo.rieber Nov 14 '14 at 16:10

1 Answers1

0

Instead of list() pass parameters as tuple() like this:

return list(Ooo.objects.raw(
    sql,
    params=(tuple(ooo_ids),)
))
quick
  • 1,104
  • 10
  • 19