PostgreSQL error: No operator matches the given name and argument type(s). You might need to add explicit type casts.

[arctelix@…]

It seems this error is still around!

Traceback (most recent call last):

  File "/app/.heroku/src/django-tastypie/tastypie/resources.py", line 202, in wrapper
    response = callback(request, *args, **kwargs)

  File "/app/.heroku/src/django-tastypie/tastypie/resources.py", line 441, in dispatch_list
    return self.dispatch('list', request, **kwargs)

  File "/app/.heroku/src/django-tastypie/tastypie/resources.py", line 473, in dispatch
    response = method(request, **kwargs)

  File "/app/.heroku/src/django-tastypie/tastypie/resources.py", line 1244, in get_list
    to_be_serialized = paginator.page()

  File "/app/.heroku/src/django-tastypie/tastypie/paginator.py", line 186, in page
    count = self.get_count()

  File "/app/.heroku/src/django-tastypie/tastypie/paginator.py", line 118, in get_count
    return self.objects.count()

  File "/app/.heroku/python/lib/python2.7/site-packages/django/db/models/query.py", line 351, in count
    return self.query.get_count(using=self.db)

  File "/app/.heroku/python/lib/python2.7/site-packages/django/db/models/sql/query.py", line 418, in get_count
    number = obj.get_aggregation(using=using)[None]

  File "/app/.heroku/python/lib/python2.7/site-packages/django/db/models/sql/query.py", line 384, in get_aggregation
    result = query.get_compiler(using).execute_sql(SINGLE)

  File "/app/.heroku/python/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 818, in execute_sql
    cursor.execute(sql, params)

  File "/app/.heroku/python/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py", line 52, in execute
    return self.cursor.execute(query, args)

DatabaseError: operator does not exist: integer = text LINE 1: ... INNER JOIN "django_comments" ON ("pins_pin"."id" = "django_...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

[Russell Keith-Magee]

It's difficult to fix "This error" if you don't tell us what "this error" is. A stack trace by itself doesn't help - we need sample code and/or an explanation to demonstrate how to reproduce the problem.

[arctelix@…]

The issue seems identical to https://code.djangoproject.com/ticket/6523. I am using the latest build of tastypie & using heroku postgreSQL The code that generates the error is in one of my my api resources. The error is generated by the line:

orm_filters['comments__user__id__exact'] = filters['cmnts']

In the below code:

class PinResource(ModelResource):    

    comments = fields.ToManyField('pinry.api.api.CmntResource', 'comments', full=True, null=True)

    def build_filters(self, filters=None):
        if filters is None:
            filters = {}

        orm_filters = super(PinResource, self).build_filters(filters)

        if 'cmnts' in filters:
            orm_filters['comments__user__id__exact'] = filters['cmnts']

class CmntResource(ModelResource):
    user = fields.ToOneField('my.api.api.UserResource', 'user', full=True)
    site_id = fields.CharField(attribute = 'site_id')
    content_object = GenericForeignKeyField({
        Pin: PinResource,
    }, 'content_object')
    username = fields.CharField(attribute = 'user__username', null=True)
    user_id = fields.CharField(attribute = 'user__id', null=True)

The CommentResource is based on the built in django-comments model. It apears the error is due to the fact that the object_pk is defined as models.TextField(_('object ID')) in the model and PostgreSQL requires it to be cast as in integer since the commented object's pk is an integer.

Let me know if you need more.

[arctelix@…]

Also the query set for the comment resource is: queryset = Comment.objects.all()

[arctelix@…]

I managed to work around the limitation caused by the object_id in django-comments being a text field as follows:

if 'cmnts' in filters:
    comments = Comment.objects.filter(user__id=filters['cmnts'], content_type__name = 'my',   site_id=settings.SITE_ID ).values_list('object_pk', flat=True)
    comments = [int(c) for c in comments]
    orm_filters['pk__in'] = comments

However, it would be great if django implimented someting similar to what has been done here:
​https://github.com/coleifer/django-generic-aggregation/blob/master/generic_aggregation/utils.py

[Anssi Kääriäinen]

Could you produce a test case using standard django models?

I think the problem is that there is need for casting the id to text in the join condition, so that object_id and the id field's types match. This doesn't seem to be easy to do, as currently only direct = conditions in joins are supported.

[Anssi Kääriäinen]

I think I will need to close this as needsinfo. I can't reproduce this, and until there is a way to reproduce this there is little we can do to fix anything. The other option is to mark this as accepted without any way to actually do anything to the ticket. So, picking what I assume to be lesser of the two evils...

[Simon Charette]

Managed to reproduce with the provided patch against master.

As Anssi said this looks hard to fix but I'm tentatively accepting.