Opened 2 years ago

Closed 23 months ago

#20271 closed Bug (needsinfo)

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

Reported by: arctelix@… Owned by: nobody
Component: Database layer (models, ORM) Version: 1.4
Severity: Normal Keywords: postgresql, pk
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


It seems this error is still around!

Traceback (most recent call last):

File "/app/.heroku/src/django-tastypie/tastypie/", line 202, in wrapper

response = callback(request, *args, kwargs)

File "/app/.heroku/src/django-tastypie/tastypie/", line 441, in dispatch_list

return self.dispatch('list', request, kwargs)

File "/app/.heroku/src/django-tastypie/tastypie/", line 473, in dispatch

response = method(request, kwargs)

File "/app/.heroku/src/django-tastypie/tastypie/", line 1244, in get_list

to_be_serialized =

File "/app/.heroku/src/django-tastypie/tastypie/", line 186, in page

count = self.get_count()

File "/app/.heroku/src/django-tastypie/tastypie/", line 118, in get_count

return self.objects.count()

File "/app/.heroku/python/lib/python2.7/site-packages/django/db/models/", 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/", 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/", 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/", line 818, in execute_sql

cursor.execute(sql, params)

File "/app/.heroku/python/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/", 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.

Change History (6)

comment:1 Changed 2 years ago by russellm

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to needsinfo
  • Status changed from new to closed

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.

comment:2 Changed 2 years ago by arctelix@…

The issue seems identical to 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.

comment:3 Changed 2 years ago by arctelix@…

  • Resolution needsinfo deleted
  • Status changed from closed to new

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

comment:4 Changed 2 years ago by 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:

comment:5 Changed 23 months ago by akaariai

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.

comment:6 Changed 23 months ago by akaariai

  • Resolution set to needsinfo
  • Status changed from new to closed

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...

Note: See TracTickets for help on using tickets.
Back to Top