PostgreSQL组与MySQL不同吗?

I've been migrating some of my MySQL queries to PostgreSQL to use Heroku. Most of my queries work fine, but I keep having a similar recurring error when I use group by:

我已经将一些MySQL查询迁移到PostgreSQL以使用Heroku。我的大多数查询都很好，但是当我使用group by时，我经常会遇到类似的问题:

ERROR: column "XYZ" must appear in the GROUP BY clause or be used in an aggregate function

错误:“XYZ”列必须出现在GROUP BY子句中，或者在聚合函数中使用

Could someone tell me what I'm doing wrong?

有人能告诉我我做错了什么吗?

MySQL which works 100%:

MySQL可以100%:

SELECT `availables`.*
FROM `availables`
INNER JOIN `rooms` ON `rooms`.id = `availables`.room_id
WHERE (rooms.hotel_id = 5056 AND availables.bookdate BETWEEN '2009-11-22' AND '2009-11-24')
GROUP BY availables.bookdate
ORDER BY availables.updated_at

PostgreSQL error:

PostgreSQL错误:

ActiveRecord::StatementInvalid: PGError: ERROR: column "availables.id" must appear in the GROUP BY clause or be used in an aggregate function:
SELECT "availables".* FROM "availables" INNER JOIN "rooms" ON "rooms".id = "availables".room_id WHERE (rooms.hotel_id = 5056 AND availables.bookdate BETWEEN E'2009-10-21' AND E'2009-10-23') GROUP BY availables.bookdate ORDER BY availables.updated_at

ActiveRecord:::StatementInvalid: PGError: ERROR: column“available”。id“必须出现在GROUP BY子句中，或用于聚合函数:选择“可用性”。*从“可使用”内部连接“房间”。id =“可用”。room_id(房间的地方。hotel_id = 5056和可用性。可使用的日期在E'2009-10-21'和E'2009-10-23'之间。bookdate ORDER BY availables.updated_at

Ruby code generating the SQL:

生成SQL的Ruby代码:

expiration = Available.find(:all,
    :joins => [ :room ],
    :conditions => [ "rooms.hotel_id = ? AND availables.bookdate BETWEEN ? AND ?", hostel_id, date.to_s, (date+days-1).to_s ],
    :group => 'availables.bookdate',
    :order => 'availables.updated_at')

Expected Output (from working MySQL query):

期望输出(通过MySQL查询):

+-----+-------+-------+------------+---------+---------------+---------------+
| id  | price | spots | bookdate   | room_id | created_at    | updated_at    |
+-----+-------+-------+------------+---------+---------------+---------------+
| 414 | 38.0  | 1     | 2009-11-22 | 1762    | 2009-11-20... | 2009-11-20... |
| 415 | 38.0  | 1     | 2009-11-23 | 1762    | 2009-11-20... | 2009-11-20... |
| 416 | 38.0  | 2     | 2009-11-24 | 1762    | 2009-11-20... | 2009-11-20... |
+-----+-------+-------+------------+---------+---------------+---------------+
3 rows in set

9 个解决方案

#1

104

MySQL's totally non standards compliant GROUP BY can be emulated by Postgres' DISTINCT ON. Consider this :

MySQL完全不符合标准的组可以被Postgres的独特的ON所模仿。考虑一下:

mysql :

mysql:

SELECT a,b,c,d,e FROM table GROUP BY a

This delivers 1 row per value of a (which one, you don't really know). Well actually you can guess, because MySQL doesn't know about hash aggregates, so it will probably use a sort... but it will only sort on a, so the order of the rows could be random. Unless it uses a multicolumn index instead of sorting. Well, anyway, it's not specified by the query.

它提供了1行每值a的值(你不知道它的值)。实际上你可以猜到，因为MySQL不知道哈希聚合，所以它可能会使用排序。但它只对a排序，所以行的顺序可能是随机的。除非它使用的是多色索引而不是排序。无论如何，查询没有指定它。

postgres :

postgres:

SELECT DISTINCT ON (a) a,b,c,d,e FROM table ORDER BY a,b,c

This delivers 1 row per value of a, this row will be the first one in the sort according to the ORDER BY specified by the query. Simple.

这将为a的每个值传递一行，这一行将是按照查询指定的顺序排序的第一行。简单。

Note that here, it's not an aggregate I'm computing. So GROUP BY actually makes no sense. DISTINCT ON makes a lot more sense.

注意，这里不是我计算的集合。所以分组实际上是没有意义的。区别ON更有意义。

Rails is married to MySQL, so I'm not surprised that it generates SQL that doesn't work in postgres.

Rails与MySQL联姻，所以我并不奇怪它生成的SQL不能在postgres中工作。

#2

PostgreSQL is more SQL compliant than MySQL. All fields - except computed field with aggregation function - in the output must be present in the GROUP BY clause.

PostgreSQL比MySQL更兼容SQL。输出中的所有字段——除了具有聚合功能的计算字段——必须在GROUP BY子句中出现。

#3

MySQL's GROUP BY can be used without an aggregate function (which is contrary to the SQL standard), and returns the first row in the group (I don't know based on what criteria), while PostgreSQL must have an aggregate function (MAX, SUM, etc) on the column, on which the GROUP BY clause is issued.

可以使用MySQL的组没有一个聚合函数(与SQL标准),并返回组中的第一行(我不知道基于什么标准),而PostgreSQL必须有一个聚合函数(MAX、求和等)列的GROUP BY子句。

#4

Correct, the solution to fixing this is to use :select and to select each field that you wish to decorate the resulting object with and group by them.

正确的是，解决这个问题的方法是:选择并选择您希望用它们装饰结果对象的每个字段，并对它们进行分组。

Nasty - but it is how group by should work as opposed to how MySQL works with it by guessing what you mean if you don't stick fields in your group by.

但是这是group by的工作方式，而不是MySQL的工作方式。

#5

If I remember correctly, in PostgreSQL you have to add every column you fetch from the table where the GROUP BY clause applies to the GROUP BY clause.

如果我没记错的话，在PostgreSQL中，您必须添加从表中获取的每一列，其中GROUP BY子句应用于GROUP BY子句。

#6

Not the prettiest solution, but changing the group parameter to output every column in model works in PostgreSQL:

不是最漂亮的解决方案，但是将组参数更改为输出模型中的每一列在PostgreSQL中是有效的:

expiration = Available.find(:all,
:joins => [ :room ],
:conditions => [ "rooms.hotel_id = ? AND availables.bookdate BETWEEN ? AND ?", hostel_id, date.to_s, (date+days-1).to_s ],
:group => Available.column_names.collect{|col| "availables.#{col}"},
:order => 'availables.updated_at')

#7

According to MySQL's "Debuking GROUP BY Myths" http://dev.mysql.com/tech-resources/articles/debunking-group-by-myths.html. SQL (2003 version of the standard) doesn't requires columns referenced in the SELECT list of a query to also appear in the GROUP BY clause.

根据MySQL的“由神话的调试组”http://dev.mysql.com/techresources/articles/debunkinggroup .html。SQL(标准的2003版本)不需要在SELECT查询列表中引用的列也出现在GROUP BY子句中。

#8

For others looking for a way to order by any field, including joined field, in postgresql, use a subquery:

对于其他想要通过任何字段排序的方法，包括在postgresql中加入的字段，使用一个子查询:

SELECT * FROM(
SELECT DISTINCT ON(availables.bookdate) `availables`.* 
FROM `availables` INNER JOIN `rooms` ON `rooms`.id = `availables`.room_id 
WHERE (rooms.hotel_id = 5056 
AND availables.bookdate BETWEEN '2009-11-22' AND '2009-11-24')
) AS distinct_selected
ORDER BY availables.updated_at

or arel:

subquery = SomeRecord.select("distinct on(xx.id) xx.*, jointable.order_field")
      .where("").joins(")
result = SomeRecord.select("*").from("(#{subquery.to_sql}) AS distinct_selected").order(" xx.order_field ASC, jointable.order_field ASC")

#9

I think that .uniq [1] will solve your problem.

我认为。uniq[1]可以解决你的问题。

[1] Available.select('...').uniq

Take a look at http://guides.rubyonrails.org/active_record_querying.html#selecting-specific-fields

看看http://guides.rubyonrails.org/active_record_querying.html# select-specific -fields

#1

104