时间戳

首页 > 解决方案 > 运算符不存在：字符变化 + 字符变化

python - 运算符不存在：字符变化 + 字符变化

问题描述

我将数据库从 sqlite 更改为 postgresql 以用于我的网站的生产，但出现此错误。当我在本地使用 sqlite 时，它​​没有收到此错误。使用 Django。

ProgrammingError at /boards/1/companies/1/

operator does not exist: character varying + character varying
LINE 1: ...Col1, (AVG(((("boards_review"."move_in_condition" + "boards_...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

Request Method:     GET
Request URL:    http://www.flythecoop.io/boards/1/companies/1/
Django Version:     2.2.6
Exception Type:     ProgrammingError
Exception Value:    

operator does not exist: character varying + character varying
LINE 1: ...Col1, (AVG(((("boards_review"."move_in_condition" + "boards_...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

Exception Location:     /home/reviews/venv/lib/python3.8/site-packages/django/db/backends/utils.py in _execute, line 84
Python Executable:  /home/reviews/venv/bin/python3.8
Python Version:     3.8.0
Python Path:    

['/home/reviews/venv/bin',
 '/home/reviews/reviews',
 '/home/reviews/venv/lib/python38.zip',
 '/home/reviews/venv/lib/python3.8',
 '/home/reviews/venv/lib/python3.8/lib-dynload',
 '/usr/lib/python3.8',
 '/home/reviews/venv/lib/python3.8/site-packages']

Server time:    Sun, 24 Nov 2019 05:52:27 +0000
Error during template rendering

In template /home/reviews/reviews/templates/baseb.html, error at line 0
operator does not exist: character varying + character varying LINE 1: ...Col1, (AVG(((("boards_review"."move_in_condition" + "boards_... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

查看错误，它说明了代码片段（AVG（（（............ Avg 所做的是显示公司所有评论的平均评分。每个字符串，例如“ move_in_condition" 都有整数值。我唯一的地方是在我的 models.py

class Company(models.Model):
    name = models.CharField(max_length=255, unique=True)
    #bio = models.TextField(max_length=4000)
    last_updated = models.DateTimeField(auto_now_add=True)
    board = models.ForeignKey(Board, on_delete = models.CASCADE, related_name='companies')
    starter = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        on_delete=models.CASCADE,
        related_name='companies',
    )
    views = models.PositiveIntegerField(default=0)

    def __str__(self):
        return self.name

    def get_avg(self):
        return self.reviews.annotate(
            overall_rating = Avg(
                F('move_in_condition') + 
                F('treatment') + 
                F('response_speed') +
                F('maintenance_quality')
            )/4).aggregate(
                Avg('overall_rating'), 
                Avg('move_in_condition'),
                Avg('treatment'),
                Avg('response_speed'),
                Avg('maintenance_quality')
            )

查看其他响应，看来我必须添加显式类型转换，但我不确定如何实现，或者我应该以某种方式完全摆脱“+”？

编辑 - 如果那是更好的方法，则更新为使用整数字段，但仍然存在同样的问题。包括我的以下字段：

class Review(models.Model):
    RATING_CHOICES = (
        (1, '1'),
        (2, '2'),
        (3, '3'),
        (4, '4'),
        (5, '5'),
    )
    STAY = (
        ('less than 6 months', 'less than 6 months'),
        ('6 months', '6 months'),
        ('10 months', '10 months'),
        ('12 months', '12 months'),
        ('More than 1 year', 'More than 1 year'),
    )
    YES_NO = (
        ('Yes', 'Yes'),
        ('No', 'No'),
    )
    SECURITY = (
        ('100%', '100%'),
        ('75%', '75%'),
        ('50%', '50%'),
        ('25%', '25%'),
        ('0%', '0%'),
        ('Still waiting', 'Still waiting'),
    )

    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(null=True)
    created_by = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete = models.CASCADE, related_name='reviews')
    updated_by = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete = models.CASCADE, null=True, related_name='+')
    company = models.ForeignKey(Company, on_delete = models.CASCADE, related_name='reviews')
    address = models.CharField(max_length=200, blank=False, default="")
    length_of_stay = models.CharField(max_length=20, choices=STAY, blank=False, default='None')

    move_in_condition = models.IntegerField(choices=RATING_CHOICES, blank=False, default='5')
    #Landlord Interaction
    treatment = models.IntegerField(choices=RATING_CHOICES, blank=False, default ="5")
    response_speed = models.IntegerField(choices=RATING_CHOICES, blank=False, default ="5")
    maintenance_quality = models.IntegerField(choices=RATING_CHOICES, blank=False, default ="5")    

    security_deposit_returned = models.CharField(max_length=5, choices=SECURITY, blank=False, default ="None")
    #put text "ignore if still waiting"
    is_this_a_fair_amount = models.CharField(max_length=5, choices=YES_NO, blank=False, default="1")
    would_you_recommend = models.CharField(max_length=5, choices=YES_NO, blank=False, default="1")
    additional_comments = models.TextField(max_length=4000)

    def __str__(self):
        return self.address

标签： pythondjangopostgresqlcastingaverage

解决方案

Postgres 对字符串连接操作使用不同的 (ANSI/SQL) 符号。你应该||改用+. 像 Postgres 这样的运营商也有 Oracle。

postgres=# 选择 varchar 'AHOJ' + varchar 'AHOJ';
错误：运算符不存在：字符变化 + 字符变化
第 1 行：选择 varchar 'AHOJ' + varchar 'AHOJ'；
                              ^
提示：没有运算符与给定的名称和参数类型匹配。您可能需要添加显式类型转换。
postgres=# 选择 varchar 'AHOJ' || varchar 'AHOJ';
+----------+
| ？柱子？|
+----------+
| AHOJAHOJ |
+----------+
(1 行)

推荐阅读