On Tue, Oct 15, 2013 at 10:20:08PM +0200, Aryeh Leib Taurog wrote:
> 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.
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
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/20131016125206.GB1506%40localhost.
For more options, visit https://groups.google.com/groups/opt_out.
