PostgreSQL vs. MySQL GROUP BY: How to Achieve MySQL's Grouping Behavior in PostgreSQL?

Differences in GROUP BY between PostgreSQL and MySQL

There are differences in the GROUP BY syntax of MySQL and PostgreSQL, which often leads to errors such as "Column X must appear in the GROUP BY clause".

MySQL’s GROUP BY behavior

In MySQL, the following query will return a row for each unique value in the bookdate column:

<code class="language-sql">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</code>

GROUP BY requirements for PostgreSQL

However, in PostgreSQL, columns that are not explicitly included in the GROUP BY clause or used in aggregate functions must be part of the primary key. In the above query, availables.id is neither included in the GROUP BY nor used in the aggregation, thus causing the error.

Simulate MySQL behavior

In order to simulate MySQL's GROUP BY behavior in PostgreSQL, you can use DISTINCT ON instead of GROUP BY:

<code class="language-sql">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')
ORDER BY availables.bookdate, availables.updated_at</code>

DISTINCT ON ensures the uniqueness of the specified columns while preserving the order specified in the ORDER BY clause. This syntax simulates MySQL's ability to return a single row of records by a unique value in the bookdate column.

The above is the detailed content of PostgreSQL vs. MySQL GROUP BY: How to Achieve MySQL's Grouping Behavior in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!