SQL cast for custom model lookup

[Aryeh Leib Taurog]

I'm working with <https://github.com/ecometrica/django-dbarray>
and I'm wondering if there's a way to get django to add an explicit
cast to the SQL generated for field lookups.

I have a model that looks like this:

class Floats(models.Model):
arr = FloatArrayField()

Which translates to the following table definition in postgresql:

CREATE TABLE "dbarray_floats" (
"id" serial NOT NULL PRIMARY KEY,
"arr" double precision[]
)
;

When I evaluate something along these lines:

Floats.objects.filter(arr=[3.0, 9.0])

I get the following error:

DatabaseError: operator does not exist: double precision[] = numeric[]
LINE 1: ...ROM "dbarray_floats" WHERE "dbarray_floats"."arr" = ARRAY[3....
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

It looks to me like psycopg2 is for some reason presenting the query
parameter as numeric[], so I would need to somehow slip a type-cast
into the SQL statement so that the WHERE clause would look something
like this:

'WHERE "dbarray_floats"."arr" = %s::double precision[]'

But there doesn't seem to be any mechanism for a custom model field to
influence the way its placeholders are presented in SQL statements.

Interestingly, this is not a problem when saving array data, only
when performing lookups.

Is there any way either to get psycopg2's data adapter to present the
query parameters as double precision[] instead of numeric[] or to put
an explicit cast into the SQL, without rewriting django's sql
compiler?

I'm using Django 1.3 with psycopg 2.4.1 and PostgreSQL 9.1

Please cc: me on replies.

Regards,
Aryeh Leib

[Aryeh Leib Taurog]

After closer inspection of the code, I found what I was looking for:

class Extra(object):
def __init__(self, extra, value):
self.extra = extra
self.value = value

def as_sql(self, qn, connection):
return self.extra, [self.value]

class FloatArrayField(ArrayFieldBase, models.FloatField):
__metaclass__ = ArrayFieldMetaclass
def get_db_prep_lookup(self, lookup_type, value, connection, prepared=False):
if not prepared:
value = self.get_prep_lookup(lookup_type, value)
return Extra('%s::double precision[]', value)

This indeed works.

I realize that an exact lookup on floats (in an array or otherwise) is
generally not good practice, so in any case the best thing here might
be to simply disallow lookups altogether. I was more interested in
learning if there was an easy way to perform the cast.

Regards,
Aryeh Leib