Query problems with Hibernate (JPA) and PostgreSQL

Question

I'm trying to use PostgreSQL as the database for Hibernate/JPA. However, I get an error when executing a simple query. My query is as follows:

SELECT DISTINCT p FROM UserProfile p ORDER BY :order

When I execute the query, I'll pass in a value like "lastLoginDate" for :order. However, I get the following exception when trying to execute the query:

ERROR org.hibernate.util.JDBCExceptionReporter  - ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
  Position: 781

This query works just fine using MySQL as the database, but we'd like to move towards PostgreSQL. My overall configuration and database connection is correct, as I see the tables being created properly. Is there something I'm missing? Is there a configuration property I need to set? Any help you can give would be MUCH appreciated. Thanks!

http://archives.postgresql.org/pgsql-sql/2007-02/msg00169.php — Bozho, May 12 '10 at 17:31
possible duplicate of [postgreSQL group by different from mysql?](http://stackoverflow.com/questions/1769361/postgresql-group-by-different-from-mysql) — Milen A. Radev, May 12 '10 at 18:02

leonbloy · Accepted Answer · 2010-05-12T18:04:36.250

7

Postgresql prohibits that query because it is ambiguous:

there's actually a definitional reason for it.  Consider

        SELECT DISTINCT x FROM tab ORDER BY y;

For any particular x-value in the table there might be many different y
values.  Which one will you use to sort that x-value in the output?

It's not very clear what you want (an example?), but if you intend to sort all records by ':order' column, and then remove duplicated values, you can do that first with DISTINCT ON: , and then reorder:

 SELECT p FROM  
    ( SELECT DISTINCT ON (p) * from UserProfile ORDER BY p , :order) 
    AS UserProfileUniq 
  ORDER BY :order ;

edited May 12 '10 at 18:04

answered May 12 '10 at 17:47

leonbloy

73,180
20
142
190

So what I was doing was something like this: * Create a named query (which corresponds to the query I posted) * I set the parameters, including the ':order' parameter which indicated which column I wanted to sort on My expectation was that my query would get translated to something like: SELECT DISTINCT p FROM UserProfile p ORDER BY lastLoginDate; That is the behavior with MySQL. I'll run the code again with increased logging and post what I get. I'll also try leonbloy's method. Someone also recommended removing DISTINCT if I don't need it. Do you think that will work? – Shadowman May 12 '10 at 19:58
To understand if it will work, we must understand the result you expect, with some example (with data). – leonbloy May 12 '10 at 20:01

Query problems with Hibernate (JPA) and PostgreSQL

1 Answers1