Django raw query - how to pass postgres array value as parameter?

Question

I'm working with raw SQL queries in a Django+PostgreSQL app, and I've come across the problem of having to pass as parameter a PostgreSQL Array value (of type tags = ArrayField(m.CharField(max_length=80) in app / tags character varying(80)[] in db).

I'm using code like this:

Product.objects.raw('SELECT * FROM myapp_code_products WHERE tags @> %s', [
    ['red'] # also tried with simply string '{"red"}' but that didn't work either, also tried with tuple
])

...in the hope of getting SQL like this generated:

SELECT * FROM myapp_code_products WHERE (tags @> '{"red"}')

but I either get simply nothing substituted like (tags @>) or list or tuple substitution like (tags @> ['red']) or (tags @> ('red')) and obviously none of this result in a valid contains query.

And, if anyone is wondering, the substituted parameter is user-supplied, so just completely bypassing parameter substitution here is not an option security-wise.

Note: also, using raw queries is to be considered a requirement here, using querysets and model queries is not an option here.

(Detail: I'm using Django 1.10.5 with Python 3.6.0 and PostgreSQL 9.3 on Windows 10 64bit)

So, you don't want to use ArrayField's contains lookup which builds @> queries for you, right? — Udi
– Udi, Commented Mar 7, 2017 at 15:57

Udi · Accepted Answer · 2017-03-09 11:19:21Z

1

Here is an example that works for me on postgres 9.6:

>>> from django.db import connection
>>> a = ["a","b","c"]
>>> b = ["b"]
>>> c = connection.cursor()
>>> c.execute("""SELECT %s::VARCHAR[] @> %s::VARCHAR[];""", (a,b))
>>> c.fetchall()
[(True,)]

Keep in mind that if tags is an ArrayField, this should work as well:

Product.objects.filter(tags__contains=['red'])

edited Mar 9, 2017 at 11:19

answered Mar 7, 2017 at 16:28

Udi

30.8k9 gold badges105 silver badges134 bronze badges

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

2 Comments

NeuronQ Over a year ago

thanks! this worked. what I tried before was array%s::varchar[] without realizing that array is added automatically when substituting a list as param value, so it ended up producing arrayARRAY[...]::varchar[]. I was just not paying attention

NeuronQ Over a year ago

and if you could please edit you answer to also include a >>> c = connection.cursor() line to be 100% executable code :)

Collectives™ on Stack Overflow

Django raw query - how to pass postgres array value as parameter?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related