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

by

7đź‘Ť

âś…

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.

👤Arctelix

36đź‘Ť

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.

👤IMSoP

0đź‘Ť

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.

👤Hubbitus

Leave a comment