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?

Answer 1

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.