Filtering not working with postgreSQL: " No operator matches the given name and argument type(s). You might need to add explicit type casts."

[gustavklopp]

There's a "bug" in postgreSQL (in fact, it's more a feature: strong typed, contrary to MySQL which is "weakly typed", since PostgreSQL 8.3):

Now if you've got: models.py

class ArticlesTable(models.Model):
    id = models.IntegerField(primary_key=True) 
    comments_set = CommentsRelation()
    ...

and if you want to display the Articles, for which there's comment, for example, you do: views.py latest_comments = ArticlesTable.objects.filter(comments_set__isnull=False)

But PostgreSQL doesn't like that! error:

ProgrammingError at /

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

How can we avoid this error when using simple filtering? Hint here: http://stackoverflow.com/questions/16044754/heroku-postgresql-django-comments-tastypie-no-operator-matches-the-given-na

But I don't now how to change that...

[vdboor]

I'm cleaning up old issues, I'm presuming this is already fixed.

This looks like an Django issue.

[bashu]

I'm cleaning up old issues, I'm presuming this is already fixed.

@vdboor no, it's not! Here an example:

import uuid

class ArticlesTable(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)

    comments_set = CommentsRelation()

and running this query:

 ArticlesTable.objects.filter(comments_set__isnull=False)

gives me:

UndefinedFunction                         Traceback (most recent call last)
/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py in _execute(self, sql, params, *ignored_wrapper_args)
     84             else:
---> 85                 return self.cursor.execute(sql, params)
     86

UndefinedFunction: operator does not exist: uuid = text
LINE 1: ... JOIN "django_comments" ON ("recipes_recipe"."id" = "django_...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

[vdboor]

Thanks! I see now what's happening. This is because Django's "GenericForeignKey" uses a Text field for the ID field. In postgres, this breaks with fields that store the data in a UUIDField.

This is clearly an issue for https://github.com/django/django-contrib-comments, as they've implemented the base model that we build upon.

[bashu]

@vdboor as a hint: https://alexgaynor.net/2010/may/04/cool-new-django-taggit-api/

[vdboor]

Ah that's indeed how it should be implemented. This is one for https://github.com/django/django-contrib-comments though, as we mainly provide nice Ajax UI fluff on top of it!