Django trying to upper(integer) in Postgres causing "no function matches the given name and argument types"

Question

Django==3.0.3
djangorestframework==3.11.0
psycopg2-binary==2.8.4
sqlparse==0.3.0

Using Postgres 11 and Python 3.8.

The field in question is the following in the model:

class User(AbstractUser):
    id = models.AutoField(primary_key=True)

This is the serializer:

class SetupUserSerializer(serializers.Serializer):
    id = serializers.IntegerField(write_only=True)

    def validate(self, data):
        user_qs = USERS.objects.filter(id__iexact=data['id'])
        if user_qs.exists() and user_qs.count() == 1:
            user_obj = user_qs.first()
            # do some stuff

When run it gets to the if user_qs.exists() and user_qs.count() == 1: and the following error comes up:

The above exception (function upper(integer) does not exist
LINE 1: ...a" FROM "users" WHERE UPPER("users"."id"::text) = UPPER(1021...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
) was the direct cause of the following exception:

Not sure why it is casting to text and trying to make uppercase.

This is the JSON being sent:

{
    "id": 123456
}

This is the traceback:

  File "/usr/local/lib/python3.8/site-packages/django/core/handlers/exception.py", line 34, in inner
    response = get_response(request)
  File "/usr/local/lib/python3.8/site-packages/django/core/handlers/base.py", line 115, in _get_response
    response = self.process_exception_by_middleware(e, request)
  File "/usr/local/lib/python3.8/site-packages/django/core/handlers/base.py", line 113, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/usr/local/lib/python3.8/site-packages/django/views/decorators/csrf.py", line 54, in wrapped_view
    return view_func(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/django/views/generic/base.py", line 71, in view
    return self.dispatch(request, *args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/rest_framework/views.py", line 505, in dispatch
    response = self.handle_exception(exc)
  File "/usr/local/lib/python3.8/site-packages/rest_framework/views.py", line 465, in handle_exception
    self.raise_uncaught_exception(exc)
  File "/usr/local/lib/python3.8/site-packages/rest_framework/views.py", line 476, in raise_uncaught_exception
    raise exc
  File "/usr/local/lib/python3.8/site-packages/rest_framework/views.py", line 502, in dispatch
    response = handler(request, *args, **kwargs)
  File "/app/authentication/views.py", line 99, in post
    if serializer.is_valid(raise_exception=True):
  File "/usr/local/lib/python3.8/site-packages/rest_framework/serializers.py", line 234, in is_valid
    self._validated_data = self.run_validation(self.initial_data)
  File "/usr/local/lib/python3.8/site-packages/rest_framework/serializers.py", line 436, in run_validation
    value = self.validate(value)
  File "/app/authentication/serializers.py", line 249, in validate
    if user_qs.exists() and user_qs.count() == 1:
  File "/usr/local/lib/python3.8/site-packages/django/db/models/query.py", line 777, in exists
    return self.query.has_results(using=self.db)
  File "/usr/local/lib/python3.8/site-packages/django/db/models/sql/query.py", line 537, in has_results
    return compiler.has_results()
  File "/usr/local/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1114, in has_results
    return bool(self.execute_sql(SINGLE))
  File "/usr/local/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1144, in execute_sql
    cursor.execute(sql, params)
  File "/usr/local/lib/python3.8/site-packages/django/db/backends/utils.py", line 100, in execute
    return super().execute(sql, params)
  File "/usr/local/lib/python3.8/site-packages/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/usr/local/lib/python3.8/site-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/local/lib/python3.8/site-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.8/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.8/site-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)

Exception Type: ProgrammingError at /auth/setup_user/
Exception Value: function upper(integer) does not exist
LINE 1: ...a" FROM "users" WHERE UPPER("users"."id"::text) = UPPER(1021...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Any suggestions on how to prevent this from happening?

Answer 1

The exception is actually coming from the UPPER() call on the right-hand side of the =, which is why the caret aligns with the beginning of the second UPPER; that's the one that is causing the exception, not the one that's casted to text.

The underlying issue, however, is using id__iexact; that's what causes the ORM to want to try to uppercase both sides of the =. You want <User model>.objects.filter(id=data['id']) instead.