I'm using Django 1.11 and Postgresql 9.6 In my app, there is a model called Person, it have several fields. In database, it's a materialized view.
我正在使用Django 1.11和Postgresql 9.6在我的应用程序中,有一个名为Person的模型,它有几个字段。在数据库中,它是物化视图。
class Person(models.Model):
personid = models.CharField(max_length=18, primary_key=True)
count = models.BigIntegerField()
native = models.CharField(max_length=2)
...
When execute
persons = Person.objects.values('personid', 'native')\
.annotate(total=Count('native'))
It says psycopg2.ProgrammingError: column "person.native" must appear in the GROUP BY clause or be used in an aggregate function
它说psycopg2.ProgrammingError:列“person.native”必须出现在GROUP BY子句中或用于聚合函数
When only select one column or not set the personid as primary key or not execute annotate it won't get error.
当只选择一列或不将personid设置为主键或不执行注释时,它不会出错。
I print the query sql:
我打印查询sql:
SELECT
"person"."native",
"person"."personid",
COUNT("person"."native") AS "total"
FROM "person"
GROUP BY "person"."native", "person"."personid"
What can I do?
我能做什么?
I make the view into table and set the personid as primary key, and then no problems.
我将视图设置为表并将personid设置为主键,然后没有问题。
1 个解决方案
#1
6
This is a known bug in Django >= 1.8 and Django < 2.0. It has been fixed in Django 2.0. I had the same problem and brought it up in the django-users mailing list.
这是Django> = 1.8和Django <2.0中的已知错误。它已在Django 2.0中修复。我有同样的问题,并在django-users邮件列表中提出。
What happened is that Django performed some optimizations, especially based on PostgreSQL. In PostgreSQL, you only need to use the pk columns in the GROUP BY clause, but that is only for tables. (The query runs more quickly if you do that.) You cannot have a PK in a view in PostgreSQL, which is why it is a problem for us since we are using un-managed models coupled with views in the backend.
发生了什么是Django进行了一些优化,特别是基于PostgreSQL。在PostgreSQL中,您只需要在GROUP BY子句中使用pk列,但这仅适用于表。 (如果你这样做,查询运行得更快。)你不能在PostgreSQL的视图中使用PK,这就是为什么它对我们来说是一个问题,因为我们使用的是非托管模型和后端视图。
References:
#1
6
This is a known bug in Django >= 1.8 and Django < 2.0. It has been fixed in Django 2.0. I had the same problem and brought it up in the django-users mailing list.
这是Django> = 1.8和Django <2.0中的已知错误。它已在Django 2.0中修复。我有同样的问题,并在django-users邮件列表中提出。
What happened is that Django performed some optimizations, especially based on PostgreSQL. In PostgreSQL, you only need to use the pk columns in the GROUP BY clause, but that is only for tables. (The query runs more quickly if you do that.) You cannot have a PK in a view in PostgreSQL, which is why it is a problem for us since we are using un-managed models coupled with views in the backend.
发生了什么是Django进行了一些优化,特别是基于PostgreSQL。在PostgreSQL中,您只需要在GROUP BY子句中使用pk列,但这仅适用于表。 (如果你这样做,查询运行得更快。)你不能在PostgreSQL的视图中使用PK,这就是为什么它对我们来说是一个问题,因为我们使用的是非托管模型和后端视图。
References: