heroku, postgreSQL, django, comments, tastypie: No operator matches the given name and argument type(s). You might need to add explicit type casts

Question

I have a simple query on django's built in comments model and getting the error below with heroku's postgreSQL database:

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

After googling around it seems this error has been addressed many times before in django, but I'm still getting it (all related issues were closed 3-5 years ago) . I am using django version 1.4 and the latest build of tastypie.

The query is made under orm filters and works perfectly with my development database (sqlite3):

class MyResource(ModelResource):    

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

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

        orm_filters = super(MyResource, 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({
        My: MyResource,
    }, 'content_object')
    username = fields.CharField(attribute = 'user__username', null=True)
    user_id = fields.CharField(attribute = 'user__id', null=True)

Anybody have any experience with getting around this error without writing raw SQL?

Motorbus

Like the error says, you're trying to compare an integer to a text value. Stop doing that and the error will go away. – Canasta

Incidentally, this is a good example of why testing should be done with an environment as similar as possible to where you deploy: however much a framework claims to abstract over it, something as complex as a DBMS is bound to have different behaviour and limitations. – Micrometeorology

IMSoP, yes indeed i learned that lesson today! Just changed my development DB over to PostgreSQL to work on this issue. – Motorbus

Wow, over 2900 views and not one up vote... I guess 2900 people found this question useless. – Motorbus

5243 and counting.... – Motorbus

i'm sorry, but i find this kinda amusing.. 14027 and counting! – Motorbus

Answer 1

Building on IMSoP's answer: This is a limitation of django's ORM layer when a Generic foreign key uses a text field for the object_id and the object's id field is not a text field. Django does not want to make any assumptions or cast the object's id as something it's not. I found an excellent article on this http://charlesleifer.com/blog/working-around-django-s-orm-to-do-interesting-things-with-gfks/.

The author of the article, Charles Leifer came up with a very cool solution for query's that are affected by this and will be very useful in dealing with this issue moving forward.

Alternatively, i managed to get my query to work 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

Originally i was searching for a way to modify the SQL similar to what Charles has done, but it turns out all i had to do was break the query out into two parts and convert the str(id)'s to int(id)'s.

Motorbus

Answer 2

PostgreSQL is "strongly typed" - that is, every value in every query has a particular type, either defined explicitly (e.g. the type of a column in a table) or implicitly (e.g. the values input into a WHERE clause). All functions and operators, including =, have to be defined as accepting specific types - so, for instance there is an operator for VarChar = VarChar, and a different one for int = int.

In your case, you have a column which is explicitly defined as type int, but you are comparing it against a value which PostgreSQL has interpreted as type text.

SQLite, on the other hand, is "weakly typed" - values are freely treated as being of whatever type best suits the action being performed. So in your dev SQLite database the operation '42' = 42 can be computed just fine, where PostgreSQL would need a specific definition of VarChar = int (or text = int, text being the type for unbounded strings in PostgreSQL).

Now, PostgreSQL will sometimes be helpful and automatically "cast" your values to make the types match a known operator, but more often, as the hint says, you need to do it explicitly. If you were writing the SQL yourself, an explicit type case could look like WHERE id = CAST('42' AS INT) (or WHERE CAST(id AS text) = '42').

Since you're not, you need to ensure that the input you give to the query generator is an actual integer, not just a string which happens to consist of digits. I suspect this is as simple as using fields.IntegerField rather than fields.CharField, but I don't actually know Django, or even Python, so I thought I'd give you the background in the hope you can take it from there.

Micrometeorology

Thanks for the info, very well put and i do understand that is what is causing the error. The issue is that django's gfk for the comments model uses a text field for the object_id and the object being commented on uses a an integer field.. So really the quest should have been how do i get around this without limiting the comment model to integer id's. – Motorbus

Answer 3

To do not hack you ORM and external software postgres allow you register your own casts and compare operations. Please look example in similar question.

Watering