I changed the db from sqlite to postgresql for production for my website and I'm getting this error. It didn't get this error when i was working locally with sqlite. Using 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.
Looking at the error, it said something about the snippet of code (AVG(((......... What the Avg does is it shows average ratings for all reviews of a company. each of the strings such as "move_in_condition" all have integer values. The only place I have that is in my 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')
)
Looking at other responses, it looks like I have to add explicit typecasts, but i'm not sure how to implement, or should i just get rid of the "+" somehow altogether?
Edit - Updated to use integerfield if thats a better way, but still have same issue. Including my fields below:
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
