Django 试图在 Postgres 中提高(整数)导致 "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

使用 Postgres 11 和 Python 3.8。

有问题的字段是模型中的以下内容:

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

这是序列化器:

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

当运行时它到达 if user_qs.exists() and user_qs.count() == 1: 并出现以下错误:

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:

不确定为什么要强制转换为文本并尝试转为大写。

这是正在发送的 JSON:

{
    "id": 123456
}

这是回溯:

  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.

关于如何防止这种情况发生有什么建议吗？

Answer 1

异常实际上来自UPPER()在 = 的右侧调用，这就是插入符号与第二个 UPPER 的开头对齐的原因;这是导致异常的那个，而不是被转换为文本的那个。

然而，潜在的问题是使用 id__iexact ;这就是导致 ORM 想要尝试将 = 的两边都大写的原因.你要<User model>.objects.filter(id=data['id'])相反。