Group by with JPA and PostgreSQL 9.0
group-by, hibernate, jpa, postgresql
Solution
That query became valid in PostgreSQL release 9.1. It appears that you are using PostgreSQL version 9.1 locally, and Heroku is using something earlier.
See the 9.1 release notes:
http://www.postgresql.org/docs/9.1/interactive/release-9-1.html
On that page, under the Queries section, it says:
Allow non-GROUP BY columns in the query target list when the primary key is specified in the GROUP BY clause (Peter Eisentraut)
The SQL standard allows this behavior, and because of the primary key, the result is unambiguous.
To get this to work under earlier versions of PostgreSQL add all expressions from the select list which don't use an aggregate function to the GROUP BY clause.
Problem
I'm trying to use the group by in a JPA query. Let's say I have a class `Teacher` and a class `Student`. A `Teacher` can have more `Student`s and a `Student` can have only one `Teacher` (one to many). The following JPA query: ``` Query q = this.em.createQuery( "SELECT teacher, COUNT(student)" + " FROM StudentJpa student" + " JOIN student.teacher teacher" + " GROUP BY teacher" + " ORDER BY COUNT(student) DESC"); ``` Generates the following SQL query: ``` select teacherjpa1_.teacher_id as col_0_0_, count(studentjpa0_.id) as col_1_0_, teacherjpa1_.teacher_id as teacher1_0_, teacherjpa1_.name as name0_ from student studentjpa0_ inner join teacher teacherjpa1_ on studentjpa0_.teacher_id=teacherjpa1_.teacher_id group by teacherjpa1_.teacher_id order by count(studentjpa0_.id) DESC ``` On PostgreSQL 9.0 I get the following error: org.postgresql.util.PSQLException: ERROR: column "teacherjpa1_.name" must appear in the GROUP BY clause or be used in an aggregate function The same error doesn't appear in PostgreSQL 9.1. Can anyone explain me why? It seems like JPA is generating the group by in a wrong way: it should include all the `Teacher` attributes, not only the id. This is my JPA/Hibernate/DB configuration, if necessary: ``` <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd"> <context:property-placeholder location="/WEB-INF/jdbc.properties" /> <bean id="dataSource" class="org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy"> <constructor-arg> <bean class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="org.postgresql.Driver" /> <property name="url" value="${db.url}" /> <property name="username" value="${db.username}" /> <property name="password" value="${db.password}" /> </bean> </constructor-arg> </bean> <bean id="jpaAdapter" class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"> <property name="databasePlatform" value="org.hibernate.dialect.PostgreSQLDialect" /> <property name="showSql" value="${db.showSql}" /> <property name="generateDdl" value="${db.generateDdl}" /> </bean> <!-- enabling annotation driven configuration /--> <context:annotation-config /> <context:component-scan base-package="my.package" /> <!-- Instructs the container to look for beans with @Transactional and decorate them --> <tx:annotation-driven transaction-manager="transactionManager" proxy-target-class="true" /> <!-- FactoryBean that creates the EntityManagerFactory --> <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"> <property name="jpaVendorAdapter" ref="jpaAdapter" /> <property name="jpaProperties"> <props> <prop key="hibernate.format_sql">true</prop> <prop key="hibernate.hbm2ddl.auto">update</prop> </props> </property> <property name="dataSource" ref="dataSource" /> </bean> <!-- A transaction manager for working with JPA EntityManagerFactories --> <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager"> <property name="entityManagerFactory" ref="entityManagerFactory" /> </bean> </beans> ``` Thanks! Update - A solution is to specify `GROUP BY teacher.id, teacher.name` instead of `GROUP BY teacher`, but that's not really convenient. Is there a better solution?