How to Migrate MySQL GROUP BY Queries to PostgreSQL Correctly?

Differences between the GROUP BY statement between PostgreSQL and MySQL

Introduction

When migrating queries from MySQL to PostgreSQL, you often encounter errors when using the GROUP BY clause. This article will dive into the differences between GROUP BY in MySQL and PostgreSQL and provide ways to resolve this error.

Error

When executing the following query in PostgreSQL:

<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>

PostgreSQL may return the following error:

<code>ERROR: column "availables.id" must appear in the GROUP BY clause or be used in an aggregate function</code>

Difference

In MySQL, the GROUP BY clause allows certain non-aggregated fields to be included in query results. However, PostgreSQL follows the SQL standard and requires that all non-aggregate fields must appear in a GROUP BY clause or be used in aggregate functions.

Solution: DISTINCT ON

To simulate MySQL’s GROUP BY behavior in PostgreSQL, we can use the DISTINCT ON expression. DISTINCT ON allows us to select a unique row per group based on the specified column. In the given example, the following query will work in PostgreSQL:

<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>

This query will return the th row for each unique value of availables.bookdate, effectively simulating MySQL's GROUP BY behavior. Note that to ensure consistent results, we added availables.updated_at to the ORDER BY clause so that DISTINCT ON always selects the same row. Without this, DISTINCT ON may return different rows each time it is executed.

The above is the detailed content of How to Migrate MySQL GROUP BY Queries to PostgreSQL Correctly?. For more information, please follow other related articles on the PHP Chinese website!