带有Postgresql的Django，列必须出现在GROUP BY子句中或在聚合函数中使用 [英] Django with Postgresql, column must appear in the GROUP BY clause or be used in an aggregate function

问题描述

我正在使用Django 1.11和Postgresql 9.6
在我的应用中，有一个名为Person的模型，它具有多个字段。在数据库中，它是一个物化视图。

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.

class Person(models.Model):
    personid = models.CharField(max_length=18, primary_key=True)
    count = models.BigIntegerField()
    native = models.CharField(max_length=2)
    ...

执行时

persons = Person.objects.values('personid', 'native')\
    .annotate(total=Count('native'))

它表示 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.

我打印查询sql：

SELECT
"person"."native",
"person"."personid",
COUNT("person"."native") AS "total"
FROM "person"
GROUP BY "person"."native", "person"."personid"

我该怎么办？

我将视图放入表并将personid设置为主键，然后没有问题。

I make the view into table and set the personid as primary key, and then no problems.

推荐答案

这是Django> = 1.8和Django< 2.0。它已在Django 2.0中修复。我遇到了同样的问题，并在django-users邮件列表中提出来。

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执行了一些优化，尤其是基于PostgreSQL的优化。在PostgreSQL中，您只需要在GROUP BY子句中使用pk列，但这仅用于表。 （如果这样做，查询将更快地运行。）在PostgreSQL的视图中不能拥有PK，这就是为什么这对我们来说是个问题，因为我们在后端使用了非托管模型和视图。

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.

参考：

• https://github.com/django/django/commit/daf2bd3efe53cbfc1c9fd00222b8315708023792
• https://groups.google.com/forum/#!topic/django-developers/lx3ZSq- W9X4


• https://groups.google.com/d/msg/django-developers/lx3ZSq-W9X4/yh4I2CsoBwAJ


• https://code.djangoproject.com/ticket/28107

• https://github.com/django/django/commit/daf2bd3efe53cbfc1c9fd00222b8315708023792
• https://groups.google.com/forum/#!topic/django-developers/lx3ZSq-W9X4
• https://groups.google.com/d/msg/django-developers/lx3ZSq-W9X4/yh4I2CsoBwAJ
• https://code.djangoproject.com/ticket/28107

这篇关于带有Postgresql的Django，列必须出现在GROUP BY子句中或在聚合函数中使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！